Page 114 - Excel Workbook for Dummies

11_798452 ch06.qxp 3/13/06 7:48 PM Page 97
Chapter 6: Copying and Correcting Formulas 97

1. Choose Insert➪Name➪Define to open the Define Name dialog box, and then type
discount_rate (don’t forget the underscore) as the range name. (This name
replaces the title of the table that automatically appears as the suggested range
name because its cell, A1, is selected in the worksheet.)
2. Press Tab twice to select =’2006 Spring Sale’!$A$1 in the Refers To text box, and
then type =15% and select OK.
Excel now contains a range name called discount_rate that sets the discount per-
centage equal to a constant 15 percent. Note, however, that this range name does
not refer to any particular cell in the worksheet.

3. Position the cell cursor in cell D3 and there construct a formula that multiplies
the retail price of the table with the code number 12-305 in cell C3 by the 15%
discount rate.
To insert a range name that you’ve defined in the workbook as an operand or
function argument in a formula you’re building, choose Insert➪Name➪Paste and
then select the range name in the Paste Name list box and select OK.
4. Copy the formula that computes the amount of the discount for the table code
numbered 12-305 in cell D3 down to the cell range D4:D7.
Note in each of the copies of the formula that although Excel adjusts the row
number of each reference to the cell in the Retail Price column, it treats the
range name, discount_rate, as an absolute reference.
5. Create a formula in cell E3 that computes the sale price of the table code num-
bered 12-305 by subtracting its discount amount from its retail price and then
copy this formula down the cell range E4:E7.
Next, you’re going to have Excel assign range names to the table cells in the
range C3:E7 from the row and column headings in row 2 and column B of the Sale
Table.
6. Select the cell range B2:E7 and then choose Insert➪Name➪Create to open the
Create Names dialog box.
Note that the Top Row and Left Column check boxes are automatically selected
in this dialog box, indicating that Excel will use the headings it finds in the top
row (7) and the left column (B) of the current cell selection when assigning the
ranges names.

7. Select OK in the Create Names dialog box to accept its default settings and close
the box, and then make Sheet2 of the Exercise6-4.xls workbook active.
8. Rename Sheet2 to Range Names, enter the heading, Range Name List, in cell A1,
and position the cell cursor in cell A2.

9. Choose Insert➪Name➪Paste and then select the Paste List button in the Paste
Name dialog box.
Excel pastes an alphabetical list of range names in the cell range A2:B11. Note
that this alphabetical listing the range names in the workbook is static so that
you’d have to generate it again with the Paste List button were you to edit the
names in this workbook and then want an up-to-date list.
10. Use AutoFit to widen column A of the Range Names sheet so that all the entries
in the first column of this list are completely displayed in the worksheet.
Next, you create a new window in the Exercise6-4.xls workbook that enables you
to compare the Sale Table in the 2006 Spring Sale and the Range Name Listing
side by side on the screen.