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!