Page 107 - Excel Workbook for Dummies

11_798452 ch06.qxp 3/13/06 7:48 PM Page 90
90 Part II: Using Formulas and Functions

Try It

Exercise 6-1: Copying Formulas in the R1C1 Reference Style

If Excel isn’t running, launch the program. Open the Exercise6-1.xls workbook file in
your Chapter 6 folder in the My Practice Spreadsheets folder inside My Documents on
your hard disk or on the Excel Workbook CD-ROM. This file contains a copy of the
2006 Production Schedule table without any formulas:

1. Position the cell cursor in cell K3 and use the AutoSum button to create a SUM
formula that totals the Part 100s scheduled to be produced over the nine-month
period from April to December in row 3.
AutoSum creates the following master formula in cell K3:

=SUM(B3:J3)
2. Use the Fill handle on the cell cursor to copy this formula down column K to the
cell range K4:K7.
3. Position the white-cross mouse pointer on the Fill handle of the cell cursor and
then, when the AutoFill Options drop-down button appears, click it. Select the
Fill without Formatting item at the bottom of the drop-down list that appears
(doing this restores the border along the top edge of cell K7 that Excel took out
when copying the formatting from cell K3 to this range).
Now, take a moment to examine the copies you made of the original SUM formula
in cell K3 by using the arrow keys to move the cell cursor through each cell K4,
K5, K6, and K7. What do you note that is different in each copy of the formula
and what is the same?
To help you understand what Excel is really doing when you make copies of a
formula containing relative cell references down the rows of a column, turn on
the R1C1 Reference system. In this system, both the rows and columns of the
worksheet are given numbers so that the cell reference A1, for example, becomes
R1C1 (because column A is the first column from the left edge of the worksheet)
and cell D6 becomes R6C4 (because column D is the fourth column from the left
edge of the worksheet).
4. Select the R1C1 Reference Style check box on the General tab of the Options
dialog box.

You can tell you are “not in Kansas anymore” using the familiar row number,
column letter reference type system — all the columns are now numbered from
left to right just as the rows are from top down.
5. Position the cell cursor in cell K3 and examine the contents of the original SUM
formula on the Formula bar.
In R1C1 notation, the formula entered in this cell now reads:
=SUM(RC[-9]:RC[-1])
The first thing to note about cell references in formulas in R1C1 notation is that
they are completely egocentric; always referring to the cell that contains the for-
mula (they could care less about the worksheet at large). This formula says, in
essence, “Use the SUM function to total from the cell that is nine columns to the
left of the current column through the cell that is one column to the left all in the
same row.” The minus sign in front of the number following the column reference
denotes columns to the left of the current one just as the minus in front of a
number following a row reference denotes rows above (positive integers in these
references denote columns to the right and rows below the current one).
6. Press the ↓ key to move the cell pointer down column K through all the cells, K4,
K5, and K6 that contain copies of the formula you constructed in cell K3, noting
the contents of each copy on the Formula bar as you move the cursor down a row.