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.