Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

Most intermediate and advanced Excel users are familiar with the concept of named cells or ranges. Naming cells and ranges is an excellent practice and offers several important advantages. As you'll see in this chapter, Excel supports other types of names-and the power of this concept may surprise you.

What's in a Name?

You can think of a name as an identifier for something in a workbook. This "something" can consist of a cell, a range, a chart, a shape, and so on. If you provide a name for a range, you can then use that name in your formulas. For example, suppose your worksheet contains daily sales information stored in the range B2:B200. Further, assume that cell C1 contains a sales commission rate. The following formula returns the sum of the sales, multiplied by the commission rate:

=SUM(B2:B200)*C1

This formula works fine, but its purpose is not at all clear. To help clarify the formula, you can define one descriptive name for the daily sales range and another descriptive name for cell C1. Assume, for this example, that the range B2:B200 is named DailySales and cell C1 is named CommissionRate. You can then rewrite the formula to use the names instead of the actual range addresses:

=SUM(DailySales)*CommissionRate

As you can see, using names instead of cell references makes the formula self-documenting and much easier to understand.

Using named cells and ranges offers a number of advantages:

• Names make your formulas more understandable and easier to use, especially for people who didn't create the worksheet. Obviously, a formula such as =Income–Taxes is more intuitive than =D20–D40.

• When entering formulas, a descriptive range name (such as Total_Income) is easier to remember than a cell address (such as AC21). And typing a name is less likely to result in an error than entering a cell or range address.

• You can quickly move to areas of your worksheet either by using the Name box, located at the left side of the formula bar (click the arrow for a drop-down list of defined names) or by choosing Home Editing Find & Select Go To (or F5) and specifying the range name.

• When you select a named cell or range, its name appears in the Name box. This is a good way to verify that your names refer to the correct cells.

• You may find that creating formulas is easier if you use named cells. You can easily insert a name into a formula by using the Formulas Defined Names Use in Formula command.

• Macros are easier to create and maintain when you use range names rather than cell addresses.