Creating Multisheet Names

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.

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.

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.

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.

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.