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

By Ali, Founder, Excel Lessons

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

$B$6 locks both parts, so copying never moves it.

In `D2`, type:

=B2*C2+$B$6

That is units times rate plus the standing charge.

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

$B$6 locks both parts, so copying never moves it. In D2, type: =B2*C2+$B$6. That is units times rate plus the standing charge.

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

Without $ signs, every reference moves when you copy. Read =B3*C3+B7 and say what it returns.

Hint. Look at what is in B7.
3

In D4, repeat the charge formula for Nine Elms. The standing charge stays locked.

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

D3 has 20 typed in place of B6, which is now 25. Fix D3, with the charge locked.

Hint. B6 holds the charge.
5

In D6, total D2:D4 with SUM.

Hint. Three cells in column D.
6

Excel treats an empty cell as 0 in maths. Read =B5*C5+$B$6 and say what it returns in D5.

Hint. Empty times empty, plus the locked cell.