Working with Range and Cell Names

After you create range or cell names, you can work with them in a variety of ways. This section describes how to perform common operations with range and cell names.

Creating a List of Names

If you create a large number of names, you may need to know the ranges that each name refers to, particularly if you're trying to track down errors or document your work.

You might want to create a list of all names (and their corresponding addresses) in the workbook. To create a list of names, first move the cell pointer to an empty area of your worksheet. (The two-column name list, created at the active cell position, overwrites any information at that location.) Use the Formulas Defined Names Use in Formula Paste command (or press F3). Excel displays the Paste Name dialog box (see Figure 3-9) that lists all the defined names. To paste a list of names, click the Paste List button.

Figure 3-9: The Paste Name dialog box.

Caution 

The list of names does not include worksheet-level names that appear in sheets other than the active sheet.

The list of names pasted to your worksheet occupies two columns. The first column contains the names, and the second column contains the corresponding range addresses. The range addresses in the second column consist of text strings that look like formulas. You can convert such a string to an actual formula by editing the cell by pressing F2 and then pressing Enter. The string then converts to a formula. If the name refers to a single cell, the formula displays the cell's current value. If the name refers to a range, the formula returns a #VALUE! error; or, in the case of multisheet names, a #REF! error.

Using Names in Formulas

After you define a name for a cell or range, you can use it in a formula. For example, the following formula calculates the sum of the values in the range named UnitsSold:

 =SUM(UnitsSold) 

Recall from the section on scope that when you write a formula that uses a worksheet-level name on the sheet in which it's defined, you don't need to include the worksheet name in the range name. If you use the name in a formula on a different worksheet, however, you must use the entire name (sheet name, exclamation point, and name). For example, if the name UnitsSold represents a worksheet-level name defined on Sheet1, the following formula (on a sheet other than Sheet1) calculates the total of the UnitsSold range:

 =SUM(Sheet1!UnitsSold) 

As you type a formula, you can select the name from the list at Formula Defined Names Use in Formula to inserts that name into your formula. At the bottom of the list is the Paste command, which displays the Past Name dialog (or by pressing F3). Displaying the Paste Name dialog box and selecting a name has the same effect as selecting the name from the list. As I previously mention, the Paste Name dialog box lists all workbook-level names, plus worksheet-level names for the active sheet only.

New 

Typing an equal sign in Excel 2007 activates the Formula AutoComplete feature, as does typing the open parenthesis of a worksheet function. Formula AutoComplete is a list of worksheet functions and defined names that appears below the active cell. To use Formula AutoComplete, begin typing the defined name until it is highlighted on the list and then press Tab to complete the entry.

If you use a nonexistent name in a formula, Excel displays a #NAME? error, indicating that it cannot find the name you are trying to use. Often, this means that you misspelled the name.

Using the Intersection Operators with Names

Excel's range intersection operator is a single space character. The following formula, for example, displays the sum of the cells at the intersection of two ranges: B1:C20 and A8:D8:

 =SUM(B1:C20 A8:D8) 

The intersection of these two ranges consists of two cells: B8 and C8.

The intersection operator also works with named ranges. Figure 3-10 shows a worksheet containing named ranges that correspond to the row and column labels. For example, January refers to B2:E2, and North refers to B2:B13. The following formula returns the contents of the cell at the intersection of the January range and the North range:

 =January North 

Figure 3-10: This worksheet contains named ranges that correspond to row and column labels.

Using a space character to separate two range references or names is known as explicit intersection because you explicitly tell Excel to determine the intersection of the ranges. Excel, however, can also perform implicit intersections, which occur when Excel chooses a value from a multicell range based on the row or column of the formula that contains the reference. An example should clear this up. Figure 3-11 shows a worksheet that contains a range (B3:B8) named MyData. Cell D5 contains the simple formula shown here:

 =MyData 

Figure 3-11: Range B3:B8 in this worksheet is named MyData. Cell D5 demonstrates an implicit intersection.

Notice that cell D5 displays the value from MyData that corresponds to the formula's row. Similarly, if you enter the same formula into any other cell in rows 3 through 8, the formula displays the corresponding value from MyData. Excel performs an implicit intersection using the MyData range and the row that contains the formula. It's as if the following formula is being evaluated:

 =MyData 5:5 

If you enter the formula in a row not occupied by MyData, the formula returns an error because the implicit intersection returns nothing.

By the way, implicit intersections are not limited to named ranges. In the preceding example, you get the same result if cell D5 contains the following formula (which doesn't use a named range):

 =$B$2:$B$8 

Using the Range Operator with Names

You can also use the range operator, which is a colon (:), to work with named ranges. Refer to Figure 3-10. For example, this formula returns the sum of the values for North through West for January through March (nine cells):

 =SUM((North January):(West March)) 

Referencing a Single Cell in a Multicell Named Range

You can use Excel's INDEX function to return a single value from a multicell range. Assume that range A1:A50 is named DataRange. The following formula displays the second value (the value in A2) in DataRange:

 =INDEX(DataRange,2) 

The second and third arguments for the INDEX function are optional although at least one of them must always be specified. The second argument (used in the preceding formula) is used to specify the row offset within the DataRange range.

If DataRange consists of multiple cells in a single row, use a formula like the following one. This formula omits the second argument for the INDEX function, but uses the third argument that specifies the column offset with the DataRange range:

 =INDEX(DataRange,,2) 

If the range consists of multiple rows and columns, use both the second and third arguments for the INDEX function. For example, this formula returns the value in the fourth row and fifth column of a range named DataRange:

 =INDEX(DataRange,4,5) 

Applying Names to Existing Formulas

When you create a name for a cell or range, Excel does not scan your formulas automatically and replace the cell references with your new name. You can, however, tell Excel to "apply" names to a range of formulas.

Select the range that contains the formulas that you want to convert. Then choose Formulas Defined Names Define Name Apply Names. The Apply Names dialog box appears, as shown in Figure 3-12. In the Apply Names dialog box, select which names you want applied to the formulas. Only those names that you select will be applied to the formulas.

Figure 3-12: The Apply Names dialog box.

Tip 

To apply names to all the formulas in the worksheet, select a single cell before you display the Apply Names dialog.

The Ignore Relative/Absolute check box controls how Excel substitutes the range name for the actual address. A cell or range name is usually defined as an absolute reference. If the Ignore Relative/Absolute check box is enabled, Excel applies the name only if the reference in the formula matches exactly. In most cases, you will want to ignore the type of cell reference when applying names.

If the Use Row and Column Names check box is selected, Excel takes advantage of the intersection operator when applying names. Excel uses the names of row and column ranges that refer to the cells if it cannot find the exact names for the cells. Excel uses the intersection operator to join the names. Clicking the Options button displays some additional options that are available only when you have the Use Row and Column Names check box enabled.

Applying Names Automatically When Creating a Formula

When you insert a cell or range reference into a formula by pointing, Excel automatically substitutes the cell or range name if it has one.

In some cases, this feature can be very useful. In other cases, it can be annoying; you may prefer to use an actual cell or range reference instead of the name. Unfortunately, you cannot turn off this feature. If you prefer to use a regular cell or range address, you need to type the cell or range reference manually (don't use the pointing technique).

Unapplying Names

Excel does not provide a direct method for unapplying names. In other words, you cannot replace a name in a formula with the name's actual cell reference automatically. However, you can take advantage of a trick described here. You need to change Excel's Transition Formula Entry option so it emulates 1-2-3. Choose File Excel Options and then click the Advanced tab in the Excel Options dialog box. Under the Lotus Compatibility Settings section, place a check mark next to Transition Formula Entry and then click OK.

Next, press F2 to edit a formula that contains one or more cell or range names. Press Enter to end cell editing. Next, go back to the Options dialog box and remove the check mark from the Transition Formula Entry check box. You'll find that the edited cell uses relative range references rather than names.

Note 

This trick is not documented, and it might not work in all cases, so make sure that you check the results carefully.

Names with Errors

If you delete the rows or columns that contain named cells or ranges, the names will not be deleted (as you might expect). Rather, each name will contain an invalid reference. For example, if cell A1 on Sheet1 is named Interest and you delete row 1 or column A, Interest then refers to =Sheet1!#REF! (that is, an erroneous reference). If you use Interest in a formula, the formula displays #REF.

In order to get rid of this erroneous name, you must delete the name manually using the Delete button in the Name Manager dialog box. Or, you can redefine the name so it refers to a valid cell or range.

New 

Excel 2007's new Name Manager allows you to filter the names displayed using predefined filters. One of the filters provided, Names with Errors, shows only those names that contain errors, which enables you to quickly locate problem names.

Viewing Named Ranges

When you zoom a worksheet to 39 percent or lower, you see a border around the named ranges with the name displayed in blue letters, as shown in Figure 3-13. The border and name do not print; they simply help you visualize the named ranges on your sheet.

Figure 3-13: Excel displays range names when you zoom a sheet to 39 percent or less.

Using Names in Charts

When you create a chart, each data series has an associated SERIES formula. The SERIES formula contains references to the ranges used in the chart. If you have a defined range name, you can edit a SERIES formula and replace the range reference with the name. After doing so, the chart series will adjust if you change the definition for the name.

Cross Ref 

See Chapter 17 for additional information about charts.