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. However, what if you can't remember the name or what if the name is long and you have 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:
- Select Formulas, Use in Formula, and then click the name in the list that appears (see Figure 3.6).
Figure 3.6 Click the Use in Formula drop-down list and then click the range name you want to insert into the formula.
- Select 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.
- Select Formulas, Define Name, Apply Names. Excel displays the Apply Names dialog box, as 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.
- From the Apply Names list, choose the name or names you want applied.
- Select the Ignore Relative/Absolute check box to ignore relative and absolute references when applying names. (The next section discusses the Ignore Relative/Absolute option in more detail.)
- 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 select this check box, you can also click the Options button to see more choices. (The "Using Row and Column Names When Applying Names" section, later in this chapter, discusses the Use Row and Column Names option in more detail.)
- 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. It also replaces absolute range references with only names that refer to absolute references. If you leave this option selected, 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 won't 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 expect to move formulas around, you should leave the Ignore Relative/Absolute option selected.
Using Row and Column Names When Applying Names
For extra clarity in your formulas, leave the Use Row and Column Names check box selected 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. For example, in Figure 3.8, the range C6:C10 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, which is the Total Rent row, contains the formula =C7+D7+E7.
As shown in Figure 3.8, the Total for the Rent row, which is cell F7, currently contains the formula =C7+D7+E7. If you applied range names to this worksheet and selected the Use Row and Column Names option, you expect this formula to be changed to the following:
=January Rent + February Rent + March Rent
However, if you try this, 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.
Selecting 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 such as 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:
- Select 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. where r is the radius of the sphere:
Assuming you have a cell named Radius somewhere in the workbook, you could create a formula named SphereVolume. Then you could 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