Sat 27 May 2017

Excel Quiz 52

By |Saturday, May 27th, 2017|Categories: Quizzes|Tags: , , , , , , , , , , |0 Comments

Excel Quiz 52 - A Quiz on Formulas Tab in Excel

This quiz checks your knowledge about Formulas tab.

Sat 06 May 2017

Downloads 15 - Excel Formulas Bible

By |Saturday, May 06th, 2017|Categories: Downloads|Tags: , , , , , , , , , , , , |0 Comments

This is one single document which contains close to 100 formulas dealing with various situations. Useful for Intermediate and Advanced users.

Download it from Excel - Formulas Bible

Sat 21 Jan 2017

Downloads 11 - Printable & Pin-up Sheet for Excel Functions

By |Saturday, January 21st, 2017|Categories: Downloads|Tags: , , , , , , |0 Comments

You can download this one page pin-up sheet and take the printout and pin up to your desk for quick reference - Excel Functions Quick Reference

(more…)

Sat 24 Dec 2016

Downloads 10 - Printable & Pin-up Sheet for Excel Shortcuts

By |Saturday, December 24th, 2016|Categories: Downloads|Tags: , , , , , , , |0 Comments

You can download this one page pin-up sheet and take the printout and pin up to your desk for quick reference. - Excel Shortcuts Quick Reference

(more…)

Sat 23 Apr 2016

Challenge 41 - Sum the Maximum Number where duplicates Exist

By |Saturday, April 23rd, 2016|Categories: Challenges|Tags: , , , , , |1 Comment

Suppose, you have been given following and you will need to find duplicates in column A and sum the maximum values from column B. If duplicates don't exist, values will be taken as they are. The values which needs to be summed up are colored. The answer would be 123 in this.

1

The workbook related to this challenge can be downloaded from Challenge 41 - Sum the Maximum Number where duplicates Exist

The answer to this challenge would be published after a month i.e. on 23-May-16.

Sat 26 Dec 2015

Tips & Tricks 124 - VBA - Check if a Range is Blank When Range Contains Formulas returning Blanks

By |Saturday, December 26th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , , |0 Comments

In VBA, often, we have situations where we need to check if a range is blank or not. The popular formula which we see following is used to check whether range is blank or not.

WorksheetFunction.CountA(Range("A1:A100"))

But if your range contains even a single formula which is returning blank, the above will not return the range as blank even though it contains literal blanks.

To handle this, you can use below to check if your range is blank even though the range contains formulas returning blanks.

Range("A1:A100").Rows.Count = WorksheetFunction.CountBlank(Range("A1:A100"))

 

Sat 19 Sep 2015

Tips & Tricks 109 - Macro to Change between A1 and R1C1 Notations

By |Saturday, September 19th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , , |0 Comments

Sometimes, you find that all of a sudden your column headings are changed into numbers and your formulas are Rs and Cs. And you need to change them back to alphabetical columns.

The option to do it very simple -

File > Excel Options > Formulas > Uncheck R1C1 Reference Style

1

But if that happens very often, you can use a macro which you can click and toggle between A1 and R1C1 style effortlessly. The macro code to do it one line only -

Sub ChangeReferenceStyle()
Application.ReferenceStyle = xlA1 + xlR1C1 - Application.ReferenceStyle
End Sub

Credit - Rick Rothstein (http://blog.contextures.com/archives/2009/12/04/excel-vba-switch-column-headings-to-numbers/)

The above link also talks about how to add the macro to QAT so that it is always available to you.

Sun 19 Jul 2015

Article 18 - LTRIM and RTRIM through Excel Formulas

By |Sunday, July 19th, 2015|Categories: Articles|Tags: , , , , , , , , |0 Comments

So, we have TRIM function in Excel. Almost all programming languages provide LTRIM and RTRIM functions also but Excel doesn't provide LTRIM and RTRIM. The same is provided in VBA but most of the Excel users are not using VBA. They are simple folks who want to accomplish their day to day job through Excel functions only.

Before I delve into LTRIM and RTRIM, let's recap TRIM.

TRIM, basically, removes all spaces from your string and if there are more than one space between your words, it will convert them into only one space. So, it removes all leading spaces, all trailing spaces and all spaces between words except one. If there is only one space between words, it will leave that space untouched.

(more…)

Sat 11 Apr 2015

Excel Quiz 9

By |Saturday, April 11th, 2015|Categories: Quizzes|Tags: , , , , , , |0 Comments

The Ninth quiz in the series to test your formula knowledge mostly in SUM and COUNT areas. After you hit Finish Quiz, you can click on View Questions to compare your answers to correct answers.

Sat 03 Jan 2015

Excel Quiz 5 - Crossword - I

By |Saturday, January 03rd, 2015|Categories: Quizzes|Tags: , , , |3 Comments

This time, I decided to put a Crossword on Excel Functions. This crossword is interactive and you can use browser to play with this. Click in the Grid to start.

Click on Check Puzzle just above Questions to check your score.

If you want to print the Crossword on a paper to play with, download from here Excel Functions Crossword 1

The answer key can be downloaded from here Excel Functions Crossword 1 Answers

If the below Crossword is not properly visible because of WordPress plugin limitation, you can play it properly here Excel Functions Crossword 1

(more…)