Copying Formulas: What Travels and What Does Not

About this lesson

Copying a formula copies its meaning, not its text. Relative references move to match the new position. Locked references keep pointing at the same cell.

By the end you can

say what travels with a copied formula and what stays behind.
paste a formula somewhere new and get the reference you meant.

Practises SUM

The idea

One formula usually has both kinds. Units and rate belong to the row and should move. A standing charge written once belongs to the sheet and should not. Deciding which is which takes a second per reference. It saves the afternoon somebody would spend finding out why one column is slightly wrong.

The habit that causes the most damage is not a missing dollar sign. It is typing a number into a formula instead of pointing at a cell. =B3*C3+20 is correct today and wrong the day the charge changes. Nobody can see it, because the 20 appears in no cell. A number that is a fact about the business, not about one row, belongs in a cell with a label next to it.

The mistake to watch for

Typing a number into a formula instead of pointing at the cell that holds it. It is right today and wrong the day the number changes. Nothing on the sheet shows it, because the typed number appears in no cell. A fact about the business, like a rate, a charge or a limit, belongs in a labelled cell that every formula reads.

Where this comes up again

The SUM Function Spreadsheet Basics
Fill Down and Fill Right Spreadsheet Basics
Bold, AutoSum and the Date Stamp Keyboard Shortcuts
AVERAGE Everyday Math

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

Each site pays for its units at its own rate, plus the standing charge in B6. Every site pays that charge. Work out Wharf Road's charge in D2.

To begin, type it exactly:

=B2*C2+$B$6

Row	A	B	C	D
1	Site	Units	Rate	Charge
2	Wharf Road	140	0.62
3	Bell Green	96	0.62
4	Nine Elms	213	0.71
5
6	Standing charge	25
7
8

Every step

1

Each site pays for its units at its own rate, plus the standing charge in B6. Every site pays that charge. Work out Wharf Road's charge in D2. To begin, type it exactly: =B2*C2+$B$6.

Hint. One of the three references needs dollar signs.
2

Somebody copied it down without locking the standing charge, so D3 arrived as =B3*C3+B7. Read that and say what it returns.

Hint. Look at what is in B7.
3

Copy the same idea into D4 for Nine Elms.

Hint. Row 4's units and row 4's rate.
4

Somebody rebuilt D3 by hand. They typed the standing charge as a number instead of pointing at B6. The charge has since risen from 20 to 25, and D3 never noticed. Repair it so it reads the cell.

Hint. B6 holds the charge.
5

In D6, use SUM to total the three charges you have worked out.

Hint. Three cells in column D.
6

One more. The fill was dragged one row too far, into D5, where there is no site at all. D5 reads =B5*C5+$B$6. What does it show?

Hint. Empty times empty, plus the locked cell.