Page 159 - Excel Timesaving Techniques for Dummies

30_574272 ch26.qxd 10/1/04 10:48 PM Page 144
144
Technique 26: Using Range Names
As you drag through the cells in the active work- To assign a constant to a range name, follow these
sheet, Excel automatically collapses the Define steps:
Name dialog box so that you can see what you’re
doing. As you select the cell range, Excel inserts 1. Choose Insert➪Name➪Define to open the
its range reference (using absolute cell references) Define Name dialog box.
after the sheet range, as shown in Figure 26-4.
2. Type the descriptive name for the new constant
7. Click OK to close the Define Name dialog box. (observing the same range-naming conventions)
in the Names in Workbook text box.
3. Press Tab until the Refers To text box is selected.
Then type = (equal sign) and enter the constant
value you want to assign to this name. (See
Figure 26-5.)
4. Click OK to close the Define Name dialog box.

• Figure 26-4: Selecting the sheets to be included in the
range name.

After naming a 3-D reference, you can use its
range name in formulas instead of having to
go to the trouble of manually selecting the
individual cell range in each sheet. This is a
real timesaver when building formulas that • Figure 26-5: Defining the 25% constant in a range name
accumulate values from different sheets. called discount_25.

If you need to define several constants at
Assigning Range Names once, click Add instead of OK in Step 4 to
insert each name in the Names in Workbook
to Constants list box. When you’re finished defining the
constants, click OK.
Not only are range names great for selecting and
referring to cell ranges in the worksheet, but you can
also use them to good advantage by naming constant Using Your Range Names
values that you need to use in your formulas. For
example, in the sample spring sale furniture table in Formulas
(refer to Figure 26-3) instead of listing the 15% and
25% discount rates in column D, you can create a The great thing about using range names in your
range name that holds these constants and then formulas is that they automatically document their
refer to them in formulas that compute the dollar function. This is especially helpful to coworkers who
amount of the discount in the cell range D3:D8. have to use your spreadsheets but who had no part