Microsoft Office Tutorials and References
In Depth Information
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 de-
scribed here (which works only for workbook-level names). You need to (temporarily) change Excel's Trans-
ition Formula Entry option so that it emulates Lotus 1-2-3.
1. Choose File ⇒ Options and then click the Advanced tab in the Excel Options dialog box.
2. Under the Lotus Compatibility Settings section, place a check mark next to Transition Formula Entry and
then click OK.
3. Select a cell that contains a formula that uses one or more cell or range names.
4. Press F2 and then press Enter.
In other words, edit the cell but don't change anything.
5. Repeat Steps 3 and 4 for other cells that use range names.
6. Go back to the Options dialog box and remove the check mark from the Transition Formula Entry check
You'll find that the edited cells use 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 referen-
ce). If you use Interest in a formula, the formula displays #REF.
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 fil-
ters. One of the filters provided, Names with Errors, shows only those names that con-
tain errors, which enables you to quickly locate problematic names.
Viewing named ranges