Using Names

One of the most useful features in Excel is its ability to provide meaningful names for various items. For example, you can name cells , ranges, rows, columns , charts , and other objects. You can even name values or formulas that don't appear in cells in your worksheet (see the "Naming constants" section, later in this chapter).

Naming cells and ranges

Excel provides several ways to name a cell or range:

Choose Formulas Named Cells Name a Range to display the New Name dialog box.
Use the Name Manager dialog box (Formulas Defined Names Name Manager or press Ctrl+F3). This is not the most efficient method because it requires clicking the New button in the Name Manger dialog box, which displays the New Name dialog box.
Select the cell or range and then type a name in the Name box and press Enter. The Name box is the drop-down control displayed to the left of the formula bar.
If your worksheet contains text that you would like to use for names of adjacent cells or ranges, select the text and the cells to be named and choose Formulas Defined Names Create from Selection. In Figure 3-2, for example, B3:E3 is named North, B4:E4 is named South, and so on. Vertically, B3:B6 is named Qtr1, C3:C6 is named Qtr2, and so on.

Figure 3-2: Excel makes it easy to create names that use descriptive text in your worksheet.

Using names is especially important if you write VBA code that uses cell or range references. The reason? VBA does not automatically update its references if you move a cell or range that's referred to in a VBA statement. For example, if your VBA code writes a value to Range("C4") , the data will be written to the wrong cell if the user inserts a new row above or a new column to the left of cell C4. Using a reference to a named cell, such as Range("InterestRate") , avoids these potential problems.

Applying names to existing references

When you create a name for a cell or a range, Excel doesn't automatically use the name in place of existing references in your formulas. For example, assume that you have the following formula in cell F10:

 =A1--A2

If you define the names Income for A1 and Expenses for A2, Excel will not automatically change your formula to

 =Income-Expenses

However, it's fairly easy to replace cell or range references with their corresponding names. Start by selecting the range that contains the formulas that you want to modify. Then choose the Formulas Defined Names Name a Range Apply Names. In the Apply Names dialog box, select the names that you want to apply and then click OK. Excel replaces the range references with the names in the selected cells.

Note

Unfortunately, there is no way to automatically unapply names. In other words, if a formula uses a name, you can't convert the name to an actual cell or range reference. Even worse , if you delete a name that is used in a formula, the formula does not revert to the cell or range address - it simply returns a #NAME? error.

My Power Utility Pak add-in (available for free by using the coupon in the back of the book) includes a utility that scans all formulas in a selection and automatically replaces names with their cell addresses.

Hidden Names

Some Excel macros and add-ins create hidden names. These are names that exist in a workbook but don't appear in the Name Manager dialog box. For example, the Solver add-in creates a number of hidden names. Normally, you can just ignore these hidden names. However, sometimes these hidden names create a problem. If you copy a sheet to another workbook, the hidden names are also copied , and they might create a link that is very difficult to track down.

You can use the following VBA procedure to delete all hidden names in the workbook:

 Sub DeleteHiddenNames()     Dim n As Name     Dim Count As Integer     For Each n In ActiveWorkbook.Names         If Not n.Visible Then             n.Delete             Count = Count + 1         End If     Next n     MsgBox Count & " hidden names were deleted." End Sub

Intersecting names

Excel has a special operator called the intersection operator that comes into play when you're dealing with ranges. This operator is a space character. Using names with the intersection operator makes it very easy to create meaningful formulas. For this example, refer to Figure 3-2. If you enter the following formula into a cell

 =Qtr2 South

the result is 732 - the intersection of the Qtr2 range and the South range.

Naming columns and rows

Excel lets you name complete rows and columns. In the preceding example, the name Qtr1 is assigned to the range B3:B6. Alternatively, Qtr1 could be assigned to all of column B, Qtr2 to column C, and so on. You also can do the same horizontally so that North refers to row 3, South to row 4, and so on.

The intersection operator works exactly as before, but now you can add more regions or quarters without having to change the existing names.

When naming columns and rows, make sure that you don't store any extraneous information in named rows or columns. For example, remember that if you insert a value in cell C7, it is included in the Qtr1 range.

Scoping names

A named cell or range normally has a workbook-level scope. In other words, you can use the name in any worksheet in the workbook.

Another option is to create names that have a worksheet-level scope. To create a worksheet-level name, define the name by preceding it with the worksheet name followed by an exclamation point: for example, Sheet1!Sales. If the name is used on the sheet in which it is designed, you can omit the sheet qualifier when you reference the name. You can, however, reference a worksheet-level name on a different sheet if you precede the name with the sheet qualifier.

The Name Manager dialog box (Formulas Defined Names Name Manager) makes it easy to identify names by their scope (see Figure 3-3). Note that you can sort the names within this dialog box. For example, click the Scope column header, and the names are sorted by scope.

Figure 3-3: The Name Manager displays the scope for each defined name.

Naming constants

Virtually every experienced Excel user knows how to create cell and range names (although not all Excel users actually do so). But most Excel users do not know that you can use names to refer to values that don't appear in your worksheet - that is, constants.

Suppose that many formulas in your worksheet need to use a particular interest rate value. One approach is to type the interest rate into a cell and give that cell a name, such as InterestRate. After doing so, you can use that name in your formulas, like this:

 =InterestRate*A3

An alternative is to call up the New Name dialog box (Formulas Defined Names Define Name) and enter the interest rate directly into the Refers To box (see Figure 3-4). Then you can use the name in your formulas just as if the value were stored in a cell. If the interest rate changes, just change the definition for InterestRate, and Excel updates all the cells that contain this name.

Figure 3-4: Excel lets you name constants that don't appear in worksheet cells.

Tip	This technique also works for text. For example, you can define the name IWC to stand for International Widget Corporation. Then you can enter =IWC into a cell, and the cell displays the full name.

Naming formulas

In addition to naming cells, ranges, and constants, you can also create named formulas. To do so, enter a formula directly into the Refers To field in the New Name dialog box.

Note	This is a very important point: The formula that you enter uses cell references relative to the active cell at the time that you create the named formula.

Figure 3-5 shows a formula (=A1 ˆ˜ B1) entered directly in the Refers To box in the New Name dialog box. In this case, the active cell is C1, so the formula refers to the two cells to its left. (Notice that the cell references are relative.) After this name is defined, entering =Power into a cell raises the value two cells to the left to the power represented by the cell directly to the left. For example, if B10 contains 3 and C10 contains 4, entering the following formula into cell D10 returns a value of 81 (3 to the 4th power):

 =Power

Figure 3-5: You can name a formula that doesn't appear in any worksheet cell.

When you display the New Name dialog box after creating the named formula, the Refers To box displays a formula that is relative to the current active cell. For example, if cell D32 is the active cell, the Refers To box displays

 =Sheet1!B32^Sheet1!C32

Notice that Excel appends the worksheet name to the cell references used in your formula. This, of course, will cause the named formula to produce incorrect results if you use it on a worksheet other than the one in which it was defined. If you would like to use this named formula on a sheet other than Sheet1, you need to remove the sheet references from the formula (but keep the exclamation points). For example:

 =!A1^!B1

After you understand the concept, you might discover some new uses for named formulas. One distinct advantage is apparent if you need to modify the formula. You can just change the formula one time rather than edit each occurrence of the formula.

CD-ROM

The companion CD-ROM contains a workbook with several examples of named formulas. The workbook is called named formulas.xlsx .

Tip	When you're working in the New Name dialog box, the Refers To field is normally in "point mode," which makes it easy to enter a range reference by clicking in the worksheet. Press F2 to toggle between point mode and normal editing mode, which allows you to use the arrow keys to edit the formula.

The Secret to Understanding Cell and Range Names

Excel users often refer to named ranges and named cells. In fact, I use these terms frequently throughout this chapter. Actually, this terminology is not quite accurate.

Here's the secret to understanding names:

When you create a name for a cell or a range in Excel, you're actually creating a named formula - a formula that doesn't exist in a cell. Rather, these named formulas exist in Excel's memory.

When you work with the New Name dialog box, the Refers To field contains the formula, and the Name field contains the formula's name. You'll find that the contents of the Refers To field always begin with an equal sign - which makes it a formula.

This is not exactly an earthshaking revelation, but keeping this "secret" in mind could help you understand what's going on behind the scenes when you create and use names in your workbooks.

Naming objects

In addition to providing names for cells and ranges, you can give more meaningful names to objects such as pivot tables and shapes . This can make it easier to refer to such objects, especially when you refer to them in your VBA code.

To change the name of a nonrange object, use the Name box, which is located to the left of the formula bar. Just select the object, type the new name in the Name box, and then press Enter.

Note	If you simply click elsewhere in your workbook after typing the name in the Name box, the name won't stick. You must press Enter.

For some reason, Excel 2007 does not allow you to use the Name box to rename a chart. You must use Chart Tools Layout Properties Chart Name.