Page 160 - Excel Timesaving Techniques for Dummies

30_574272 ch26.qxd 10/1/04 10:48 PM Page 145
in their design. When using names in your formulas, Using Your Range Names in Formulas 145
you can assign the names as you construct new for-
mulas in the worksheet, or you can add them after
the fact to existing formulas.

Using range names in new formulas

If you named a cell range or constant that you need
to refer to in a new formula, you can use its name
when building a formula. To enter the name, you can
either type it (risky business if you have any trouble
remembering the exact name), or you select its
name from the Paste Name dialog box, as shown in
• Figure 26-6: Inserting a range name into a new formula.
Figure 26-6.

Selecting the name from the Paste Name dia-
log box is much easier — especially when deal-
ing with constants that don’t show up
anywhere on the worksheet — unless you’ve
pasted a list of range names somewhere
within it (Insert➪Name➪Paste, Paste List).

Figure 26-6 illustrates how you go about using a range
name in a new formula. In this example, I want to
multiply the retail price in cell C3 by the discount_25
constant to compute a 25% discount in cell D3. To do
this, I follow these steps: • Figure 26-7: Worksheet table with formulas using
constants saved as range names.
1. Start the formula by typing = (equal sign),
select cell C3, and then type * (asterisk) to indi- Assigning range names to existing formulas
cate multiplication.
Excel doesn’t automatically replace cell references
2. Open the Paste Name dialog box (Insert➪ with the range names that you assign to them. To
Name➪Paste), select discount_25 in the Paste
replace cell references with their names, you need
Name list, and click OK.
to use the Insert➪Name➪Apply command. Then in
Excel inserts discount_25 into the formula, and the Apply Names dialog box that appears, you select
then I have to click the Enter button on the the range names that you want applied in your work-
Formula bar to complete it. sheet formulas by selecting them in the Apply Names
list box.
Figure 26-7 shows the sales table after I finish adding
the discount formulas in the cell range D3:D8 that When you first open the Apply Names dialog box,
compute the discount amount using either the dis- it contains just two check boxes: Ignore Relative/
count_25 or the discount_15 constant. By adding the Absolute and Use Row and Column Names (both
range name, I can tell in an instant which percentage of which are checked). When you click the Options
I’m using for each item as I move the cell pointer button, Excel expands the Apply Names dialog box
through these cells. to display additional options that you can use when
applying your range names, as shown in Figure 26-8.