Microsoft Office Tutorials and References
In Depth Information
Copying Formulas: More Than Just Duplication
Figure 4–27. The grades organized horizontally, starting at cell KJ9. Note John’s formula in the formula bar.
John’s bonus formula would now read
See why? Here, the dollar sign has been inserted to the left of the N, that part of the cell
reference that indicates the cell’s column letter. And we need to freeze the column letter
here because John’s formula has to be copied across a set of columns—and without the
dollar sign, Bill’s bonus formula would read
And there’s nothing in cell O7.
In fact, you can freeze both parts of a cell reference (e.g.,
). Copying this reference
in either direction—down a column or across a row—won’t change the reference at all.
This is good to know if you need to refer to that cell in a variety of locations in the
It’s true that newcomers to Excel sometimes find the subject of relative and absolute cell
addressing a bit daunting at first, so don’t be discouraged if you’re feeling the same. Just
bear in mind that you need that dollar sign only when formulas in different cells have to
refer to the same cell over and over again—as with our last example, in which all the test
scores need to refer to the same bonus-point cell—N7. Again, the advantage here is that if
you need to change the bonus, you need only change the value in N7, rather than having
to edit every student’s formula. You could even recolor N7 to remind users that it’s the
only cell they need to change (recoloring will be discussed in Chapter ).
Copying a Formula’s Result Only
There may be times when you need to save a value on a worksheet that’s been
generated by a formula. For example—suppose you needed to record those student test
scores with their bonuses to a different area of the worksheet for future reference. Well,