Microsoft Office Tutorials and References

In Depth Information

**Working with Range and Cell Names**

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)

Defined names also appear in the Formula AutoComplete drop-down list. To use Formula

AutoComplete, begin typing the defined name until it is highlighted on the list and then press

Tab to complete the entry. Or, use the down arrow key (

↓

) to select a name from the list.

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

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.