Distinguish relative and absolute cell references, use the dollar sign to fix a reference, and predict how references change when a formula is copied

What this dot point is asking

SEAB wants you to tell the difference between a relative and an absolute cell reference, to use the dollar sign to fix a reference, and to predict how references change when a formula is copied. The central idea is that a relative reference describes a position relative to the formula's cell and so shifts when copied, while an absolute reference is locked to one exact cell.

The answer

Relative references

A normal reference such as B2 is relative. The spreadsheet really stores it as a direction, like "one cell to the left, same row". When you copy the formula to another cell, that direction is reapplied from the new position, so the reference adjusts:

• Copy down one row, and the row number increases by one (B2 becomes B3).
• Copy right one column, and the column letter advances by one (B2 becomes C2).

This is what makes a single formula fill a whole column correctly.

Absolute references

Sometimes you want a reference to point at the same cell no matter where the formula is copied, for example a tax rate or an exchange rate stored once. You make a reference absolute by putting a dollar sign before the column and the row:

$E$1   column E and row 1 both fixed

A $` locks whatever follows it. `$E$1 never changes when copied.

Mixed references

You can fix just the column or just the row:

• $E1 fixes the column E but lets the row change.
• `E$1` fixes the row$1$ but lets the column change.

These are useful for filling a table down and across from a single header row or column.

Predicting a copy

To work out what a copied formula becomes, apply the copy movement to each relative reference, and leave each absolute reference unchanged:

C2: =B2*$E$1   copied down to C3  ->  =B3*$E$1

Examples in context

Example 1. Applying one tax rate. A sales sheet stores the tax rate once in E1 and uses =B2*$E$1 down a whole column of prices. Because E1 is absolute, every row uses the same rate; because B2 is relative, every row uses its own price. Changing E1 updates the whole column.

Example 2. A multiplication table. To build a times-table grid, a single formula like =$A2*B$1 is filled across and down. The $A` fixes the row labels' column and the `$1 fixes the header row, so each cell multiplies its own row label by its own column header.

Try this

Q1. State what the dollar signs do in the reference $C$4. [2 marks]

• Cue. They fix the column C and the row $4$, so the reference does not change when the formula is copied.

Q2. A formula =A1*2 in cell B1 is copied down to B3. State the formula in B3. [2 marks]

• Cue. The relative reference moves down two rows: =A3*2.

Q3. Explain when you would use an absolute reference rather than a relative one. [2 marks]

• Cue. When a formula must always point at one fixed cell (such as a single rate or total) even after being copied to other cells.