Applying names to existing references

When you create a name for a cell or a range, Excel doesn't automatically use the name in place of existing references in your formulas. For example, assume that you have the following formula in cell F10:

If you define the names Income for A1 and Expenses for A2, Excel will not automatically change your formula to


However, it's fairly easy to replace cell or range references with their corresponding names. Start by selecting the range that contains the formulas that you want to modify. Then choose the Formulas

Defined Names Name a Range Apply Names. In the Apply Names dialog box, select the names that you want to apply and then click OK. Excel replaces the range references with the names in the selected cells.

Note Unfortunately, there is no way to automatically unapply names. In other words, if a formula uses a name, you can't convert the name to an actual cell or range reference. Even worse, if you delete a name that is used in a formula, the formula does not revert to the cell or range address - it simply returns a #NAME? error.

My Power Utility Pak add-in (available for free by using the coupon in the back of the book) includes a utility that scans all formulas in a selection and automatically replaces names with their cell addresses.

Hidden Names

Some Excel macros and add-ins create hidden names. These are names that exist in a workbook but don't appear in the Name Manager dialog box. For example, the Solver add-in creates a number of hidden names. Normally, you can just ignore these hidden names. However, sometimes these hidden names create a problem. If you copy a sheet to another workbook, the hidden names are also copied, and they might create a link that is very difficult to track down.

You can use the following VBA procedure to delete all hidden names in the workbook:

Sub DeleteHiddenNames() Dim n As Name Dim Count As Integer For Each n In ActiveWorkbook.Names If Not n.Visible Then n.Delete

MsgBox Count & " hidden names were deleted." End Sub

Was this article helpful?

0 0

Post a comment