Datasheet

119
CHAPTER 4
■
WORKING DATA MAGIC WITH CALCULATIONS
Figure 4.13. Moving and copying cells with relative references
Relative references, on the other hand, give the input cells’ locations
relative to the formula cell, and when you copy a formula cell to a new
location, relative references continue to refer to locations relative to the
formula cell. This behavior is quite handy when you expect it, and frus-
trating if you aren’t aware of it.
To copy a formula cell with relative references and keep the formula
intact, change the references to absolute before you copy the cell. You
can also open the cell, drag to select the formula, copy the formula, press
Enter to close the cell, and then paste the copied formula in a different
cell, but changing the references is less work and more permanent.
If you want to use the same formula with relative references elsewhere
in the workbook or worksheet (for example, to use the same formula in
another similar table), copy the cell and paste it in the new location.
Copy with AutoFill
Most often you’ll want the relative references to do their job and change
the copied formula to suit its new location. For example, when you set up
a Quantity column and a Price column and then want to multiply the
quantities by the prices for a total price in each row, just write your formula
one time at the top of the total column, and click and drag the AutoFill
handle down the column to fill the formula cells (see Figure 4.14). If you
Results still correct
Relative references changed the results
Bright Idea
The best way to keep cell references intact and also easily identifiable is to
use cell names.
09_763217 ch04.qxp 1/18/06 11:23 PM Page 119