Microsoft Office Tutorials and References
In Depth Information
Working with Range and Cell Names
the names. Clicking the Options button displays some additional options that are available only
when you select the Use Row and Column Names check box.
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. For example, if you plan to copy the
formula, the range references won’t adjust if the reference is a name rather than an address.
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
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 Lotus 1-2-3. Choose File
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.
This trick is not documented, and it might not work in all cases, so make sure that you
check the results carefully.
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
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.
The Name Manager allows you to filter the names that it displays using predefined
filters. One of the filters provided, Names with Errors, shows only those names that
contain errors, which enables you to quickly locate problem names.