Microsoft Office Tutorials and References
In Depth Information
Copying Formulas: More Than Just Duplication
But when you’re moving formulas, you’re moving cell references, and here’s where the
difference lies. When you move a formula, the cell references in the formula don’t
change. That’s because Excel assumes you want that formula to continue to refer to the
same cells, even though it’s being relocated.
Thus, if you write
in cell D2 and move it somewhere—anywhere—else, Excel will think that you still want
the formula to add cells A2 and C2 (see Figure 4–23).
Figure 4–23. Moving the formula won’t change its cell references. Notice it’s fjrst been posted in cell D2, and in
the second screenshot has been moved to I7 without an change in its cell references.
On the other hand, say you leave your formula in place in cell D2 but move the number
in A2 instead, to cell B6. The formula will now state
That is, it will reflect the new destination of the values that contribute to a formula .
The bottom line is this: if you move a formula, or any of the cells that contribute to the
formula instead, Excel assumes either way that you won’t want to change the result of
the formula—and so it makes the adjustments necessary to see to it that the result is
unchanged by any changes in cell addresses.
Keeping a Cell Reference Constant with Absolute Addressing
As stated, understanding relative cell addressing is one of those must-know features
that Excel depends on in order to, well, make it Excel. But there’s an additional aspect
to copying formulas that’s just about as important.
Let’s say our teacher isn’t yet sure how large that grade bonus should be, and wants to
try out different point awards to see how they affect the test grades. In cell N7, the
teacher enters the number 4 instead, and rewrites the original formula in cell L8 to read
As you see, we’ve removed the number 5 and replaced it with a cell reference—cell N7,
which currently contains the value 4. Thus, John’s bonus score right now stands at 81,
as shown in Figure 4–24.
Figure 4–24. John’s bonus-awarding formula