Page 147 - Excel Timesaving Techniques for Dummies

28_574272 ch24.qxd 10/1/04 10:47 PM Page 132
132
Technique 24: Efficient Formula Copying
The easiest way to understand what’s going on when current column.” When I copy the formula across
Excel adjusts a cell reference’s row number or col- row 8 to the February column (3) and March column
umn letter is to switch the program out of its row (4), you see that in R1C1 notation Excel doesn’t
number/column letter default style of cell referenc- really adjust anything. The program simply copies
ing to the alternate (and much more logical) R1C1 the original R1C1 version of the formula to each cell
style. With this style, both the rows and columns of in the columns on the right. There, the exact copy of
the worksheet are numbered so that what you com- the original formula performs the same function:
monly call cell A1 becomes cell R1C1. summing the values in its column that are four rows
up through and including one row up.
To switch into R1C1 cell addressing, open the
General tab of the Options dialog box and Figure 24-2 confirms this fact. After copying the origi-
select the R1C1 Reference Style check box. nal formula in cell R8C2 (B8) to the cells R8C3, R8C4,
To switch back into the normal row number/ and R8C5 (C8, D8, and E8), I moved the cell pointer
column letter style, clear this check box. one column to the right. As you can verify by looking
at the Formula bar in Figure 4-2, Excel really did
To see how viewing cell references in the R1C1 style make an exact copy of the original formula to this
can help you understand how Excel makes formula cell. When viewed in R1C1 notation, you can under-
copies, consider the simple SUM formula shown in stand why the copy calculates the correct sum.
Figure 24-1. This formula in cell B8 (R8C2 in R1C1
notation) totals the January 2004 CD sales by sum-
ming the range of the four cells immediately above.

• Figure 24-2: Verifying that a copy of the SUM formula
is exactly the same as the original in the
R1C1 notation.
• Figure 24-1: Viewing a simple SUM formula in the
R1C1 cell reference notation. Next, take a look at a formula that sums the values
across different columns in the same R1C1 notation.
In normal row number/column letter notation, this In Figure 24-3, you see the original formula that sums
formula reads: the three-month CD sales in the Rock category for
the first quarter. In R1C1 notation, this SUM formula
=SUM(B4:B7) reads:

When you switch into R1C1 notation, this same for- =SUM(RC[-3]:RC[-1])
mula appears as:
In English, this formula says “sum from the cell that
=SUM(R[-4]C:R[-1]C) is three columns to the left (C[-3]) to and including
the cell one column to the left (C[-1]) in this row.”
All this says in English is “sum the cell that’s four
When I copy this formula down column 5 (E) to rows
rows up from the current row (-4) through and 5, 6, and 7, the exact copies of this formula sum the
including the cell that is one row up (-1) in the