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

=A2+C2

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

=B6+C2

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

=K8+N7

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