This chapter is from the book
This chapter is from the book
This chapter is from the book
Working with Range Names in Formulas
Chapter 2, "Using Range Names," showed you how to define and use range names in your worksheets. You probably use range names often in your formulas. After all, a cell that contains the formula =Sales-Expenses is much more comprehensible than one that contains the more cryptic formula =F12-F3. The next few sections show you some techniques that make it easier for you to use range names in formulas.
Pasting a Name into a Formula
One way to enter a range name in a formula is to type the name in the formula bar. But what if you can't remember the name? Or what if the name is long and you've got a deadline looming? For these kinds of situations, Excel has several features that enable you to select the name you want from a list and paste it right into the formula. Start your formula, and when you get to the spot where you want the name to appear, use any of the following techniques:
- Choose Formulas, Use in Formula and then click the name in the list that appears (see Figure 3.6).
Figure 3.6 Drop down the Use in Formula list and then click the range name you want to insert into your formula.
- Choose Formulas, Use in Formula, Paste Names (or press F3) to display the Paste Name dialog box, click the range name you want to use, and then click OK.
Type the first letter or two of the range name to display a list of names and functions that start with those letters, select the name you want, and then press Tab.
)
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:
- 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.
- Choose Formulas, Define Name, Apply Names. 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.
- Choose the name or names you want applied from the Apply Names list.
- 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.)
- 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, "Using Row and Column Names When Applying Names," for details.)
- 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.
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.
Naming Formulas
In Chapter 2, you learned how to set up names for often-used constants. You can apply a similar naming concept for frequently used formulas. As with the constants, the formula doesn't physically have to appear in a cell. This not only saves memory, but it often makes your worksheets easier to read as well. Follow these steps to name a formula:
- Choose Formulas, Define Name to display the New Name dialog box.
- Enter the name you want to use for the formula in the Name text box.
- In the Refers To box, enter the formula exactly as you would if you were entering it in a worksheet.
- Click OK.
Now you can enter the formula name in your worksheet cells (instead of the formula itself). For example, the following is the formula for the volume of a sphere (r is the radius of the sphere):
4π3/3
So, assuming that you have a cell named Radius somewhere in the workbook, you could create a formula named, say, SphereVolume, and make the following entry in the Refers To box of the New Name dialog box (where PI() is the Excel worksheet function that returns the value of Pi):
=(4 * PI() * Radius ^ 3) / 3