← VCE Spreadsheets, SQL & Visualisation

Relative and absolute cell references

The dollar signs lock a cell reference, so $B$1 stays fixed when a formula is copied. A plain B1 is relative and shifts to match its new position. B$1 locks only the row.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 9, VCE Data Analytics - U3O1 - Data Analytics — Module 62, VCE Data Analytics - U4O1 - Development and Evaluation — Module 9, 9-10 Digital Technologies: Acquiring, storing and validating data — Module 11, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 99

Understanding Relative and absolute cell references in VCE Spreadsheets, SQL & Visualisation

What it does: With no dollar signs, both references are relative. Copied down one row, the formula becomes =A3+B3. A dollar sign before the column letter and another before the row number lock both parts, so the reference stays on B1. $B$1 keeps pointing at the GST rate. With B1, the reference would slide down to B2, B3 and so on, and pick up the wrong cells. The $ sits in front of the 1, so the row is locked. The column B can still change when the formula is copied across. $B$1 is absolute so it never changes. Only the relative A2 shifts down to A3.

Common mistakes: E1 is relative, so when the formula is copied down it shifts to E2, E3 and so on. The rate must be locked as $E$1 so every row uses the same cell. $B$2 is locked, so every copy would multiply the first price. The price B2 should be relative so it moves to B3, B4 and so on when copied down.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Relative and absolute cell references to a class →

This module's key terms are also in a printable crossword (Modules 4–20) and word search (Modules 4–34).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
You copy the formula =B2*B1 from C2 down to C3. What does the copied formula in C3 become?
=B3*B2=B2*B1
Matches
=C2*B1 (B1 holds the tax rate, copied down)
↓
B1 is relative, so when copied down it shifts to B2, B3 and so on, and the formula stops using the tax rate. It should be $B$1.
Applying
Meanings
Term
$B$1
Meaning
The dollar signs lock a cell reference, so $B$1 stays fixed when a formula is copied. A plain B1 is relative and shifts to match its new position. B$1 locks only the row.
Practice
=B2*$E$1
Testing
Gaps
=A5*
Check Answer
Spots
3C3: =B3*$E$1
4C4: =B4*E1

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Manta Ray' badge — Graceful Coder, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. You copy the formula =B2*B1 from C2 down to C3. What does the copied formula in C3 become?
A. =B2*B1
B. =B3*B2 ✓
Plain references like B2 and B1 are relative, so they shift down one row when the formula is copied down one row.
2. Which reference stays fixed on cell B1 when a formula is copied anywhere?
A. $B$1 ✓
B. B1
The dollar signs lock both the column and the row. A plain B1 is relative and changes when copied.
3. A GST rate is in cell B1. Which formula is best for A4 so it can be copied down a column of prices?
A. =A4*$B$1 ✓
B. =A4*B1
$B$1 keeps pointing at the GST rate. With B1, the reference would slide down to B2, B3 and so on, and pick up the wrong cells.
4. In the reference B$1, what does the dollar sign lock?
A. Only the column (column B)
B. Only the row (row 1) ✓
The $ sits in front of the 1, so the row is locked. The column B can still change when the formula is copied across.
5. You copy =A1+B1 from C1 across to D1. What does the formula in D1 become?
A. =B1+C1 ✓
B. =A1+B1
Both references are relative. Moving one column to the right shifts each column letter along by one.

Match the Pairs

=B2*$B$1 → B2 is relative and shifts when copied down, but $B$1 stays locked on the same cell, such as a fixed tax rate
B$1 → A mixed reference that locks the row only, so the row stays 1 but the column can change when copied across
A relative reference with no dollar signs changes to match its new position when the formula is copied one row down → =A2+B2
An absolute reference locks both the column and the row so the cell never changes when the formula is copied → $B$1
=C2*B1 (B1 holds the tax rate, copied down) → B1 is relative, so when copied down it shifts to B2, B3 and so on, and the formula stops using the tax rate. It should be $B$1.
=C2*$B1 (copied across to column D) → Only the column is locked, so the row can still change. To lock the row of a cell in row 1, use B$1 or $B$1.

Key Term

$B$1
The dollar signs lock a cell reference, so $B$1 stays fixed when a formula is copied. A plain B1 is relative and shifts to match its new position. B$1 locks only the row.

Fill in the Blanks

1. =A5*$B$1
Hint: Type the fully locked (absolute) reference to the cell in column B, row 1, so it stays fixed when the formula is copied.
2. =A2*B$1
Hint: Type the symbol that locks only the row number of this mixed reference.
3. D2: =B2*C2 D3: =B3*C3
Hint: Type the relative reference that has shifted down one row after copying the formula.
4. C2: =B2*$F$1 C3: =B3*$F$1
Hint: Type the absolute reference that does not change when the formula is copied down.
5. B2: =$A2*B$1
Hint: Type the mixed reference that locks the column but lets the row change.

Spot the Error

Question 1
A E1: 0.1
B C2: =B2*$E$1
C C3: =B3*$E$1
D C4: =B4*E1
Line D — E1 is relative, so when the formula is copied down it shifts to E2, E3 and so on. The rate must be locked as $E$1 so every row uses the same cell.
Question 2
A C2: =$B$2*$E$1
B C3: =B3*$E$1
C C4: =B4*$E$1
Line A — $B$2 is locked, so every copy would multiply the first price. The price B2 should be relative so it moves to B3, B4 and so on when copied down.
Question 3
A Row 1: 1,2,3 in B1:D1
B Column A: 1,2,3 in A2:A4
C B2: =A$2*B$1
D Copy B2 across and down
Line C — A$2 locks the row, so copying down would keep using row 2. The column should be locked instead, as $A2, so the row changes but column A stays fixed.
Question 4
A C2: =B2/$B$10
B C3: =B3/B10
C C4: =B4/$B$10
Line B — B10 is relative, so it shifts to B11 when copied down and no longer points to the total. The total must be locked as $B$10.
Question 5
A Prices A2:A4, rates B1:D1
B B2: =$A2*(1-B1)
C Copy B2 across and down
Line B — B1 is fully relative, so copying down moves the rate to B2, B3 and so on, which are not rates. The row should be locked as B$1 so it always reads row 1.

Student with a class code? Join your class →