More Cool Excel Shortcuts

Back in April, I posted my own personal Top Ten Best Excel Shortcuts in reverse order (ala Letterman’s Top Ten…). Here is an additional List of Five of my favorite shortcuts for use with Named Ranges:

5. Name a Range
Select the range, and then click in the Name Box (far left on the formula bar) and type a one-word name

4. Quickly Name All of Your Ranges
Highlight your entire database including the headings with Ctrl + A and then click Ctrl + Shift + F3

3. Go to a Named Range
Click F5 (Great for selecting Named Ranges!)

2. Edit a Named Range
To delete or edit a named range click Ctrl+F3.

And the Number One Best Named Range Shortcut Is (Trumpets, please)...

1. Display the Paste Names Dialog Box for Use in Formulas
Click F3 and use your arrow keypad to select (Super helpful when creating long formulas)

Working with Named Ranges Rocks – Use these tools to make your Excel life easier!

Spiff Your List with Conditional Formatting Icons

Many of us use Excel to keep Lists of Stuff. It may be as simple as a grocery list or as complex as a due-diligence list for a corporate merger. Starting with Excel 2007, you can use sets of Well-Formatted Icons with your lists (and replace lists using the drab “x”).

For an example, let’s suppose we are organizing a Lutefisk Promotion Event in Minnesota, (I’m originally from Minnesota, doncha know – Oh yeah…), and you want to Spiff Up your checklist that will track who will attend.

Here is how you do it:

1) Select the range of cells in which the checkmarks will appear (from C3 to C8 in our example) and put a number 1 in each cell where you want a checkmark
2) Click Conditional Formatting on the Home tab, choose Icon Sets and pick the set that includes the Green Checkmark
3) Go back to Conditional Formatting and choose Manage Rules
4) Click Edit Rule and put a check in the Show Icon Only box and Apply

That is It! Your lists will instantly take on new character and professionalism (and it wasn’t even hard, doncha know…)

Indirect and Validation

Interactive reports are powerful tools that can provide the user with multiple sets of information in a One-Stop, Professional Format. There are many ways to create reports of this type, (background pivot tables, Boolean functions, etc), but one of the easiest is to make use of the functionality in Validation combined with the Indirect Function.

Although there are many uses for the Indirect Function, used in its simplest form it will take the Word in the cell it is referring to and return the Named Range.
Using the small spreadsheet in the graphic above, try the following:

1) Create your Named Ranges by selecting each of your ranges and typing the new Name in the Name Box on the upper-left corner of your worksheet. In the above example graphic, name each of the monthly rows (excluding the cells with the month name) for the four units. For instance, for August, select C12:F12 and name it August.

2) To get a convenient Dropdown Box, go to Data Validation in Tools and choose Allow List (you can choose the list of items that you want to appear in the dropdown box). For example, in the above graphic, the cell C2 with “April” in it contains the interactive dropdown created using the list of months.

3) Now the fun begins! In cell D2 insert the formula “=SUM(INDIRECT(C2))”. The Indirect Function reads the name of the month you choose with the dropdown box in C2 and the formula sums up the total for the four units.

Try this straightforward, easy way to make an interactive report. They’ll soon put up statues in your honor!

Troubleshooting Formulas

Let’s face it, problems happen in Excel. One of the most common issues, of course, involves the use of formulas. The problem is that (this is particularly true if you were not the original creator of the workbook and have “inherited” it) that you may not know which cells have formulas and which are simply data.

An easy way to identify the cells with formulas and display them is to hold down the Ctrl button and press ~. When you press this keyboard combination, all of your formulas will appear in their cells (pressing the combination again will bring back your original view).

Another vexing problem can be determining exactly where a formula is drawing its information from. Excel contains a nifty (do people still say “nifty”) tool for clicking on the formula cell and tracing its Precedents. For Excel 2003, go to Tools / Formula Auditing. For Excel 2007, go to Formulas / Formula Auditing.


Clicking on Trace Precedents will give you a Graphical Layout with Arrows and Worksheet representations showing you from where your information is being derived.

Ctrl + ~ and Trace Precedents; two excellent tools to help you get your Excel workbook back on track.

DateDif Revisited

A little over a year ago, I discussed the obscure DateDif function, and it was a readers’ favorite.

As I mentioned in my 2009 post, Microsoft has for reasons only they know, mysteriously chosen not to include documented information of this fascinating function in Excel. DateDif is a very useful (I think it is essential) tool for doing calculations with dates. Here is a refresher with some additional good information.

The syntax of the function is as follows:

=DateDif(First Date, Second Date, Time Interval)

Where the Time Interval is expressed as follows:

d   (Days) = Number of days between the dates
m  (Months) = Complete calendar months between the dates
y   (Years) = Complete calendar years between the dates

Note: The Second Date must be greater than the First Date, or you will get a Number Error.

An entertaining application of this function is to nest the NOW() function into it and calculate a person’s age as follows (Note: the “BirthDate” can refer to an easily changed cell value):

=DateDif(BirthDate, NOW(), “y”)

Special Note: If you put the Time Interval in the function directly, be sure to put “quotation marks” around it (e.g. “m”). If you put it into the formula via a cell reference, do not use the quotation marks (e.g. the cell should contain m, not “m”).

There are a great many ways to use the DateDif function, so give it a try some time. You may wonder how you ever lived without it!

Dynamic Named Ranges

Nearly two years ago, (see my September 15, 2008 post), I wrote about the wisdom of using Named Ranges in Excel. Using a Named Range in a formula does away with the need to make the range an absolute reference because it will always point to the correct range, regardless of where you copy the formula.

The only problem with a Named Range is that if you have a static cell reference, (e.g. $A$1:$A$20), when you add additional data, you will typically need to go to the Name Manager and Change the reference. What a drag!

Happily, there is an easy solution. Excel allows you to enter a Formula in the Named Range reference. Although there are several formulas that work for this purpose, the following one is one of the most versatile. Let’s suppose that you have you data in Column B, and you do not want to include the header in B1. By using this formula, and assuming you do not have any blank cells, (which is a bad database management practice anyway…), you will include your entire range on a Dynamic basis:

=INDIRECT(CONCATENATE("$B$2:$B$", COUNTA($B:$B)))

As a consequence, more data can be added to your database, and your Named Ranges will always remain Up-to-Date with no muss, no fuss. Wow, that Rocks!

Importing Problem? Change Text to Numbers!

Many of us have occasions where we are importing files that were not created in Excel. Usually this causes no problems, but once in a while when you import from other sources, your numeric values may appear to be numbers, but they act like text!

Happily, this is easily solved by converting the values into numbers. To resolve this problem, simply do the following:

1. Click a blank cell in the worksheet, and Type the Number 1 in the cell

2. Right-click, (“Right-click is Our Friend”), and Copy the cell with the number 1 in it

3. Select the range that contains the values you are converting to numbers

4. Right-click and choose Paste Special

5. On the Paste Special menu, click Multiply, and then click OK

So how do you know if you have been successful? Well, you can set up a simple function to see if the values now act as numbers, or you can check out the alignment of the numbers in the cells. If you are using a common format, the numbers will be aligned on the right and the text values will be aligned on the left.

Importing from a source other than Excel? Keep this little trick in mind, and Easily Convert your Values to Numbers! Rock On!