• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 3. Building Basic Formulas > Applying Names to Formulas

Applying Names to Formulas

If you've been using ranges in your formulas and you name those ranges later, Excel doesn't automatically apply the new names to the formulas. Instead of substituting the appropriate names by hand, you can get Excel to do the hard work for you. Follow these steps to apply the new range names to your existing formulas:

1.
Select the range in which you want to apply the names, or select a single cell if you want to apply the names to the entire worksheet.

2.
Choose Insert, Name, Apply. Excel displays the Apply Names dialog box, shown in Figure 3.7.



Figure 3.7. Use the Apply Names dialog box to select the names you want to apply to your formula ranges.


3.
Choose the name or names you want applied from the Apply Names list.

4.
Activate the Ignore Relative/Absolute check box to ignore relative and absolute references when applying names. (See the next section for more information on this option.)

5.
The Use Row and Column Names check box tells Excel whether to use the worksheet's row and column names when applying names. If you activate this check box, you also can click the Options button to see more choices. (See the section in this chapter titled “Using Row and Column Names When Applying Names” for details.)

6.
Click OK to apply the names.

Ignoring Relative and Absolute References When Applying Names

If you clear the Ignore Relative/Absolute option in the Apply Names dialog box, Excel replaces relative range references only with names that refer to relative references, and it replaces absolute range references only with names that refer to absolute references. If you leave this option activated, Excel ignores relative and absolute reference formats when applying names to a formula.

For example, suppose that you have a formula such as =SUM(A1:A10) and a range named Sales that refers to $A$1:$A$10. With the Ignore Relative/Absolute option turned off, Excel will not apply the name Sales to the range in the formula; Sales refers to an absolute range, and the formula contains a relative range. Unless you think you'll be moving your formulas around, you should leave the Ignore Relative/Absolute option activated.

Using Row and Column Names When Applying Names

For extra clarity in your formulas, leave the Use Row and Column Names check box activated in the Apply Names dialog box. This option tells Excel to rename all cell references that can be described as the intersection of a named row and a named column. In Figure 3.8, for example, the range C6:C13 is named January, and the range C7:E7 is named Rent. This means that cell C7—the intersection of these two ranges—can be referenced as January Rent.

Figure 3.8. Before applying range names to the formulas, cell F7 (Total Rent) contains the formula =C7+D7+E7.


→ The space character is Excel's intersection operator. For more information, seeUsing the Intersection Operator,” p.34.


As shown in Figure 3.8, the Total for the Rent row (cell F7) currently contains the formula =C7+D7+E7. If you applied range names to this worksheet and activated the Use Row and Column Names option, you'd think this formula would be changed to this:

=January Rent + February Rent + March Rent

If you try this, however, you'll get a slightly different formula, as shown in Figure 3.9.

Figure 3.9. After applying range names, the Total Rent cell contains the formula =January+ February+March.


The reason for this is that when Excel is applying names, it omits the row name if the formula is in the same row. (It also omits the column name if the formula is in the same column.) In cell F7, for example, Excel omits Rent in each term because F7 is in the Rent row.

Omitting row headings isn't a problem in a small model, but it can be confusing in a large worksheet, where you might not be able to see the names of the rows. Therefore, if you're applying names to a large worksheet, you'll probably prefer to include the row names when applying names.

Choosing the Options button in the Apply Names dialog box displays the expanded dialog box shown in Figure 3.10. This includes extra options that enable you to include column (and row) headings:

  • Omit Column Name If Same Column— Clear this check box to include column names when applying names.

  • Omit Row Name If Same Row— Clear this check box to include row names.

  • Name Order— Use these options to choose the order of names in the reference (Row Column or Column Row).

Figure 3.10. The expanded Apply Names dialog box.


  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint