Microsoft Office Tutorials and References
In Depth Information
Copying Formulas: More Than Just Duplication
The Classic Copy-and-Paste Method
Click the cell you want to copy—in this case L8.
Click the Copy button via Home Clipboard Copy sequence.
Select the destination cells—in this case L9:L12.
Click the Paste button (in the
Clipboard button group) or press
Enter. You should see what’s shown
in Figure 4–21.
Figure 4–21. Teacher with a heart: There’s that
Note that we obviously haven’t copied John’s grade to the other students—we’ve
copied his formula ; and when you copy a formula, Excel changes the cell references in
the destination cells to reflect their distance from the source cell. That means that since
our source formula reads
Bill’s formula is going to read
because his data sits one row beneath John’s. That one row down brings about a
onerow change in his formula—from K8 to K9. And since Ed’s grade-bonus formula is
positioned in cell L12, it reads
to reflect its distance in rows—four rows, to be exact—from John’s source formula.
This property of copied cell references—the fact that they record their degree of
distance from the original source cell—is called relative cell addressing (or referencing),
because the copied cells change their addresses relative to the source cell—the one
that’s being copied.
And that also means that if you were to copy a source formula horizontally (i.e., across a
series of columns , not down a series of rows as per our example), relative cell
addressing would change the column element of the cell address—its letter. Thus, if you
one column to the right, the destination cell will read