Microsoft Office Tutorials and References
In Depth Information
Copying Formulas: More Than Just Duplication
Relative cell addressing is one of those Excel features you have to know, because
copying formulas to destination cells is an extremely common part of the spreadsheet
construction process. Relative cell addressing requires that you write just one formula in
one cell—all you need to do next is copy that one formula to all the other cells with
which you’re working. Thus, if our hypothetical class consisted of 100—or even 1,000—
students, you’d still need only construct one formula, which you’d then copy to all the
Next I’ll discuss the second way in which to copy a formula. First, delete cells L9:L12,
because we’re trying out copy method number two. Then do the following:
Click back in the source cell, L8.
Click the fill handle—that small
square holding down the lower-right
corner of the cell pointer (see Figure
Figure 4–22. The fill handle revisited
Click the fill handle and drag it through cell L12.
Different technique, same result. And there’s a cool, very useful variation on that
technique you’ll want to know about. If you click L8 and double-click the fill handle
instead of dragging it, the source formula will be copied to all the cells through L12, too.
This double-click method works when consecutive cells to the immediate left or right of
the formula column have values in them. Thus, if K10 had been blank in our case, this
technique would have copied the formula only through L9.
NOTE: When I introduced copying in Chapter 2, I stated that you only have to click one
destination cell when you copy multiple source cells, because Excel knows that all the source
cells have to be pasted. But here, because you’re only copying one cell to multiple cells, you
need to go ahead and select all those cells to which you’re copying.
Moving Formulas: An Important Difference
You can move formulas with the techniques described in Chapter 2:
Select the formula-bearing cells you want to move.
Click the Cut command.
Click in the first destination cell.