Assigning range names to existing formulas
Excel doesn't automatically replace cell references with the range names that you assign to them. To replace cell references with their names, you need to use the Insert → Name → Apply command. Then in the Apply Names dialog box that appears, you select the range names that you want applied in your worksheet formulas by selecting them in the Apply Names list box.
When you first open the Apply Names dialog box, it contains just two check boxes: Ignore Relative/Absolute and Use Row and Column Names (both of which are checked). When you click the Options button, Excel expands the Apply Names dialog box to display additional options that you can use when applying your range names.
The complete Apply Names options include the following:
- Ignore Relative/Absolute: Select this check box to replace cell references with the names that you've selected in the Apply Names list box
regardless of the type of reference used in their formulas. Clear this check box if you want to replace only those cell references that use the
same type of references as your names (absolute for absolute, mixed for mixed, and relative for relative).
Most often, you want to leave this check box selected because Excel automatically assigns absolute cell references to the names that you define and relative cell references in the formulas that you build. - Use Row and Column Names: Select this check box to have the names appear in your formula that you created from row and column headings with the Create Names feature. Clear this check box if you don't want these row and column names to appear in the formulas in your worksheet.
- Omit Column Name if Same Column: Select this check box to prevent Excel from repeating the column name when the formula is in the same column. Clear this check box if you want the program to display the column name even in formulas in the same column as the heading used to create the column name.
- Omit Row Name if Same Row: Select this check box to prevent Excel from repeating the row name when the formula is in the same row. Clear this check box if you want the program to display the row name even in formulas in the same row as the heading used to create the row name.
- Name Order: You have two choices here:
- Row Column: Click this option button (the default) to have the row name precede the column name when both names are displayed in the formulas.
- Column Row: Click this option button to have the column name precede the row name.
For example I created range names for the sales table by selecting the range A2:E8, opening the Create Name dialog box, and using the Top Row and Left Column defaults (see "Creating names from row and column headings" earlier in this tutorial). Next, I selected the cell range D3:E8, which contains all the cells with formulas in this table, and then opened the Apply Names dialog box (Insert → Name → Apply).
Because I wanted the row headings included in the formula's range, I clicked the Options button to expand the Apply Names dialog box and then cleared the Omit Row Name if Same Row check box and clicked OK. As a result, Excel inserted the code range name in the formulas. For example, the formula in cell D3 now contains
=_12_305 Retail_Price*discount_25
If I had left the Omit Row Name if Same Row check box selected in the Apply Names dialog box, the formula in D3 would instead read
=Retail_Price*discount_25
The problem with omitting the row heading from the formulas is that all the formulas in column D would then read the same as the one in D3. By adding the row heading, anyone using the spreadsheet can easily verify the precise function of each discount formula.
The only problem with including the row or column headings is that, in formulas that refer to more than one cell in the same row or column, the repeating of these headings can make the formulas long and cumbersome to decipher (thus defeating the goal of using range names to document their function).
For example, the formula in cell E3 that computes the sales price for the 36-inch round table with the code 12-305 now reads
=_10_235 Retail_Price-_10_235 Discount
In such cases, you may be better off using the Apply Names default settings that omit all repeated headings to create a much cleaner, albeit generic form of the formula. In the case of E3, the sales price formula created by omitting the row heading would be much simpler:
=Retail_Price-Discount
Just keep in mind that this is how all the rest of the sales price formulas in cells D4, D5, D6, D7, and D8 appear.