F3: The Magic Key

Well, “Magic Key” might overstating it a bit, but you work with Named Ranges and a lot of Formulas, the F3 key can save you a great deal of work. Here is what it can do for you:

Let’s say that you have named several ranges (an Excel Enthusiasts best practice) in your workbook. When creating a formula, (in this example, we will find the average of a field named, “Sales”), do the following:

1. Type “ =Average( "
2. The hit the F3 key and
3. Choose your Named Range from the dropdown
4. Note that you can use your Down Arrow on your keyboard to select

This shortcut will save you little bits of time that will add up to many hours of work. (Wow! “Magic” …)

Engaging Charts

As any good presenter knows, you must first Engage your audience in order to effectively communicate with them. With Excel 2007, there is no need to settle for tiresome old charts that everyone has seen before (and probably largely ignores).

Customization is now easy, and can take very little time to apply. Follow a few simple steps to create one-of-a-kind engaging charts. For instance, to create a striking pie chart, try the following:

1. Select your table of data and go to Insert/Charts/Pie
2. On the Pie dropdown, choose a 3-D selection
3. Double-click and choose a color scheme from the Design tab
4. Right-click on the pie chart and select Format Data Series
5. On 3-D Format area, play around with the Bevel and Surface Material options

In very little time, you will find that you can create a unique and compelling chart that is sure to get “Ooohs and Ahhhs”. Give some new pie a try – you may find it very tasty!

Pop-Up Documentation Validation

At times a spreadsheet created by another (albeit brilliant Excel practitioner) can be confusing. The Data Validation tool can help make your worksheets more useful to others. Use this tool to simply add a pop-up window displaying documentation whenever the cell is selected.

Excel 2007 users can follow these steps:

1. Select the cell for the pop-up to appear
2. Click the Data tab of the ribbon
3. Select the Data Validation tool and resulting Data Validation dialog box
4. Choose the Input Message tab
5. Check the Show Input Message When Cell is Selected
6. Enter a title for the pop-up window
7. Enter the text of the documentation in the Input Message
8. Click OK

Give a helping hand to your spreadsheet users. Pop-ups can be a good thing!

That Blank Look

Experienced Excel users know that the IF function is a powerful tool when wanting to return certain words based on the parameters you set within the formula. But what if you want the target cell to remain blank if certain conditions are not met?

The answer is so simple, it will make you laugh. You use quotation marks as with text, but just put a space between them.

=IF(A1 > 365,”Data Error!”,” ”)

In our example, the IF function can help maintain data entry integrity. If the value in cell A1 is greater than 365, “Data Error!” is displayed in the cell. If A1 does not exceed the parameter chosen, there is no need for the message, and the cell remains blank.

Sometimes that “Blank Look” is just what you need. (Just don’t try it when your boss is talking to you…).