Validation with a Named Range

Using a list with the Validation tool in Excel is a useful way of maintaining consistency in a spreadsheet. Combining it with a Named Range can make it more effective!

Let’s say you are using a dropdown box in a cell that refers to a list of employees. For convenience and to assure consistent spelling, you have created the dropdown box using the List feature on Validation.

Rather than having to put up with the hassle of adding/deleting employees from the reference list and then having to adjust the list range, you can simply Name a Range “Employees”, (with sufficient room to add names in the future), and refer to “Employees” in the list box in the Validation dialogue box.

Give it a try. I think you'll like this “Home on the Range”…

Freeze Panes

This is a very simple, but highly useful Excel maneuver. If you have a long list of data and you want to keep the headings on the screen, simply:

1. Click the cell immediately under the row beneath the column headings.
2. Select View/Window/Freeze Panes (Excel 2007)

You can now scroll your data and still see the headings. Cool, eh? (“Cool”, get it)?

Find What You Want Where You Want

If you work with large amounts of data, you probably find yourself in need of searching for specific items in specific regions of your Excel worksheets. The Find function (Ctrl+F) is one of Excel's most frequently used tools for this purpose.

There are times, of course, when you do not want to search All of your data. If you want to narrow your search to a specific range in your worksheet, (a single column, for instance), you can simply select the range you want to search before pressing Ctrl+F. When the search is performed, only the selected range is included in the exploration, and all else is ignored.

Select your range and hold Ctrl+F. Bamm! There’s your data!

Use Conditional Formatting to Highlight Above-Average Values

Highlighting exeptional values can be a useful (and simply-done) analysis tool. Here is an easy way to highlight the Above-Average values in a range:

1. At the bottom of your range, enter the formula for Average: =Average(range)
2. Select the values in the range (not including the Average formula)


3. On the Home tab in Excel 2007, choose Conditional Formatting from the Styles menu


4. Select Highlight Cells Rules / Greater Than


5. Choose the dialogue box and click on the cell in which you have the Average Formula


6. Choose the Formatting you wish to apply


7. Click Enter and Presto! Values that are Above-Average are highlighted!
   

Use this simple method to highlight Above-Average values (You will be Above-Average Yourself!)

Right-click Tab Scrolling Buttons

Large Excel workbooks will commonly contain a great many worksheets that can be cumbersome to scroll through to find a particular item.

A quick and easy shortcut to the worksheet of your choice is to right-click the tab scrolling buttons in the lower-left corner of your workbook. A menu will appear showing the tab names. You can then choose your location and navigate to it directly.

Bamm! You can go right to your worksheet and skip by the rest!

Quick Chart!

This has always been one of my favorite easy crowd-pleasers.

To make a Quick Chart, simply select your table of date, and press F11. Bamm! A full-featured chart will be inserted in your workbook. Don't like the default chart? Just right-click the resulting chart and choose Change Series Chart Type.

Give it a try, and listen to them go "Oooh and Ahhh..."

Beware: There May be Links in this Workbook!

If you are working within an Excel document that you may not be familiar with, be cautious before deleting or clearing a cell containing a formula. You can use the keyboard shortcut for revealing another cell that it may be feeding by pressing Ctrl + Shift + ] (Closing Bracket).

If it doesn’t reveal any linked cells, you can safely delete the formula (Cool!).