Spreadsheets that Calculate
Copying formulas, and references that stay put
Work out what a formula points at once it has been copied, and use a dollar sign to stop a reference moving.
The most useful thing a spreadsheet does is let you write a calculation once and use it a hundred times, and copying is how. The part that surprises people is that a copied formula does not say the same thing as the original. A reference in a formula does not really mean cell B1 at all — it means the cell two to the right and one up from here, or whatever the relationship happens to be — and copying keeps the relationship rather than the address. That is exactly what you want almost every time: a formula that totals its own row goes on totalling its own row wherever you paste it.
The dollar sign pins a reference down
Almost every time is not every time. Sometimes a formula has to keep pointing at one particular cell — the single rate, the one total at the bottom — however far it is copied. Putting a dollar sign in front of a part of a reference stops that part moving. The column letter and the row number are pinned separately, so there are four possible formulas rather than two: both parts free, both parts pinned, or one of each. Which one you want is decided by which way you are going to copy, and answering that question before writing the formula is the whole of the skill.
Worked example
Cell D3 holds =B3*C3, and it is copied down into D6. What does the copy read?
Work out the move first: D3 to D6 is three rows down, and no columns across.
Measuring the move once, before looking at any reference, is what keeps this from turning into guesswork.
Try it together
Now decide the pinning yourself. Column A holds amounts, and cell C1 holds a single rate that every amount is to be multiplied by. The formula will go into C3, be copied down the column, and later be copied sideways into other columns as well.
Deal with the two references one at a time, and remember that the copy travels both ways.
1.As the formula is copied down the column, which of the two cells must it go on pointing at? Answer with a cell reference.
Have a go
Have a go on your own. Cell P2 holds =P1+$Z$1 and is copied down into P3. In the copy, which cell is $Z$1 added to?
Ready to practise?
Eight questions on what you have just read. Nothing is timed, and you can play as many times as you like.