Applying Names to Existing Formula

When you create a name for a cell or range, Excel does not scan your formulas automatically and replace the cell reference 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 go to Formula tab on the ribbon in Defined Names group and click Define Name more button and select Apply Names (figure shown below).

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.

The Ignore Relative/Absolute check box control 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 checked, Excel applies the name only if the reference in the formula match exactly. In most cases, you will want to ignore the type of cell reference when apply names.

If the Use row and column names check box is checked, 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 the names. Clicking the Options button displays some additional options that are available only when you have the use row and column names check box checked.