Excel 2010 Bible

Part II: Working with Formulas and Functions

226

To refer to a single cell, use the intersection operator. Move to any blank cell and enter the follow-

ing formula:

=Quarter1 West

This formula returns the value for the first quarter for the West region. In other words, it returns

the value that exists where the Quarter1 range intersects with the West range. Naming ranges in

this manner can help you create very readable formulas.

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, suppose you have the following formula in

cell F10:

=A1–A2

If you define a name Income for A1 and Expenses for A2, Excel won’t automatically change

your formula to =Income–Expenses. Replacing cell or range references with their correspond-

ing names is fairly easy, however.

To apply names to cell references in formulas after the fact, start by selecting the range that you

want to modify. Then choose Formulas ➪ Defined Names ➪ Define Name ➪ Apply Names. Excel

displays the Apply Names dialog box, as shown in Figure 10.19. Select the names that you want to

apply by clicking them and then click OK. Excel replaces the range references with the names in

the selected cells.

FIGURE 10.19

Use the Apply Names dialog box to replace cell or range references with defined names.