Microsoft Office Tutorials and References

In Depth Information

**Working with Range and Cell Names**

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

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.

Define Name

➜

➜

Figure 3-12:
The Apply Names dialog box.

To apply names to all the formulas in the worksheet, select a single cell before you

display 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 selected, 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