Page 148 - Excel Timesaving Techniques for Dummies

28_574272 ch24.qxd 10/1/04 10:47 PM Page 133
133
When It’s Copy Time
monthly sales for the Jazz, Classical, and Other cate- above in the current column). You know that the sec-
gories, respectively. ond cell reference following the divisor is absolute
because it simply contains the R1C1 cell reference
itself. When I copy this formula to the February and
March columns in row 10, these copies will perform
the proper division, dividing the February monthly
total in cell R8C3 (C8) by the quarterly total in R8C5
(E8) and the March monthly total in cell R8C4 (E8)
by the quarterly total in R8C5 (E8) as well.

Even if you don’t want to work regularly with
Excel in R1C1 notation, you can use this sys-
• Figure 24-3: Copying a SUM formula in R1C1 notation tem when you want to verify that all your
that totals across the columns of a row. copies of a formula are correct. With R1C1
turned on, all the copies you make of a given
You may be curious about how formulas that contain formula should appear identical to the original
on the Formula bar as you move the cell
absolute references work in R1C1 notation. Figure
pointer through the range.
24-4 shows you the original formula that calculates
what percentage of the entire quarter’s sales each
month’s total sales represents. It does this by divid-
ing the January monthly total in row 8 by the quar- When It’s Copy Time
terly total in cell R8C5 (E8). Because each copy of
this formula for the February and March percent- When it comes to copying your spreadsheet formu-
ages need to divide their specific monthly totals in las, you have a choice between using AutoFill or cut
row 8 by the same quarterly total in cell R8C5 (E8), and paste. Which method I use depends entirely
the reference to this cell needs to be absolute. upon where the formula needs to be copied to.

If I’m copying a formula in one dimension — that is,
down the rows of a single column or across the
columns of a single row — I will use AutoFill if I just
have to drag the Fill handle down a few blank rows
or across a few blank columns. If I have to make a
gazillion copies in one or the other dimension, I usu-
ally switch to cut and paste.

When copying a formula in two dimensions — that is,
down the rows of the same column and then across
• Figure 24-4: Copying a formula that uses an absolute cell the columns of the selected rows to fill out a sched-
reference in R1C1 notation. ule or table of data that uses the basic formula to
compute all its values — I almost never use AutoFill
In R1C1 notation, the original percentage formula because this requires two drag operations: the first
with the absolute cell reference appears as: to copy the formula down the rows and the second
to copy it across the columns.
=R[-2]C/R8C5
Instead, I cut corners: I copy the formula cell to the
You can see that the first cell reference is relative Clipboard, select the entire range of the table (includ-
(it says simply to take the value in the cell two rows ing the current cell with formula), and press Enter to