Page 345 - Excel 2013 All-in-One For Dummies

Range Names in Formulas 327
2013 does not let you use the range name in a formula on any other worksheet (you’ll get the #NAME? error), and Scope is the one aspect you can’t change when editing a range name via the Name Manager. (I discuss managing range names later in this chapter).
Using names in building formulas
After you assign a name to a cell or cell range in your worksheet, you can then choose the range name from the Use in Formula button’s drop-down menu on the Ribbon’s Formulas tab to paste it into the formulas that you build (Alt+MS).
For example, in the sample Sales Price table shown in Figure 1-13, after assigning the discount rate of 15% to the range name, discount_rate, you can create the formulas that calculate the amount of the sale discount. To do this, you multiply the retail price of each item by the discount_rate constant using the Use in Formula command button by following these steps:
1. Make cell D3 active.
2. Type = (equal sign) to start the formula.
3. Click cell C3 to select the retail price for the first item and then type * (asterisk).
The formula on the Formula bar now reads, =C3*.
4. Click the Use in Formula button on the FORMULAS tab or press Alt+MS.
This action opens the drop-down menu on the Use in Formula button on which you can select the discount_rate range name.
5. Choose the name discount_rate from the Use in Formula button’s drop-down menu.
The formula now reads =C3*discount_rate on the Formula bar.
6. Click the Enter button on the Formula bar to input the formula in cell D3.
Now, all that remains is to copy the original formula down column D.
7. Drag the Fill handle in cell D3 down to cell D7 and release the mouse button to copy the formula and calculate the discount for the entire table.
Book III Chapter 1
Building Basic Formulas