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:
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:
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:
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:
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.