← Years 7–8 Data & Information

Absolute cell references with $

An absolute cell reference. The $ before the column letter and row number locks the cell, so the reference does not change when the formula is copied. Press F4 to add the $ signs.

Part of: 7-8 Digital Technologies: Acquiring, storing and validating data — Module 11, ACARA Digital Technologies Years 7–8 — Acquiring and Storing Data — Module 45, ACARA Digital Technologies Years 7–8 — Analysing and Visualising Data — Module 20, ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 11

Understanding Absolute cell references with $ in Years 7–8 Data & Information

What it does: $F$1 is absolute, so every copied formula still uses the rate in F1, while B2 is relative and moves down to B3, B4 and so on. Put a $ before the column letter and before the row number, like $B$1. Both parts stay fixed when the formula is copied. Without the $ signs, B1 would move to B2, B3 and so on when copied, and it would pick up empty cells. $B$1 stays locked on the rate. F4 adds the $ signs to the reference you are editing. Pressing it again cycles through the other locked versions. C2 is relative, so it changes to C3. $E$1 is absolute, so it stays the same.

Common mistakes: The rate cell is not locked. When the formula is copied down, E1 becomes E2, E3 and so on. It should be =B2*$E$1 so the rate stays in E1. Only the column is locked. The row number is not, so F1 changes to F2 when copied down. It should be =C2*$F$1.

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 Absolute cell references with $ to a class →

This module's key terms are also in a printable crossword (Modules 9–45) and word search (Modules 9–80).

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 =A2*B1 down a column. Which kind of reference is B1?
Relative, so it changes to B2, B3 and so onAbsolute, so it always stays as B1
Matches
=B2*F1 (copied down to row 3 becomes =B3*F2)
↓
The rate cell F1 has no $ signs, so it is relative. When the formula is copied down, F1 shifts to F2 and the rate is wrong. Fix it by writing $F$1.
Applying
Meanings
Term
$B$1
Meaning
An absolute cell reference. The $ before the column letter and row number locks the cell, so the reference does not change when the formula is copied. Press F4 to add the $ signs.
Practice
=B2*$E$1
Testing
Gaps
=A2*
Check Answer
Spots
1=B2*E1
2=B3*$E$1

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

The 'Kraken' badge — Deep Sea Master, 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 =A2*B1 down a column. Which kind of reference is B1?
A. Absolute, so it always stays as B1
B. Relative, so it changes to B2, B3 and so on ✓
B1 has no $ signs, so it is a relative reference. It shifts down one row each time the formula is copied.
2. Which reference locks both the column and the row?
A. $B$1 ✓
B. B$1$
Put a $ before the column letter and before the row number, like $B$1. Both parts stay fixed when the formula is copied.
3. Cell B1 holds a GST rate of 10%. Which formula keeps pointing at B1 when copied down?
A. =A4*$B$1 ✓
B. =A4*B1
Without the $ signs, B1 would move to B2, B3 and so on when copied, and it would pick up empty cells. $B$1 stays locked on the rate.
4. Which keyboard shortcut adds the $ signs to a cell reference?
A. Press F1 after typing the reference
B. Press F4 after typing the reference ✓
F4 adds the $ signs to the reference you are editing. Pressing it again cycles through the other locked versions.
5. You copy =C2*$E$1 from row 2 to row 3. What does the formula become?
A. =C3*$E$1 ✓
B. =C3*$E$2
C2 is relative, so it changes to C3. $E$1 is absolute, so it stays the same.

Match the Pairs

=B2*$E$1 → Multiplies B2 by a fixed value in E1. When copied down, B2 changes to B3, B4 and so on, but $E$1 stays locked.
$B$1 → An absolute cell reference that always points to cell B1, even when the formula is copied to another cell
A GST rate is stored in cell F1. Every price in column B must be multiplied by that same rate when the formula is copied down. → =B2*$F$1
The keyboard shortcut that adds $ signs to a cell reference while you are editing a formula → F4
=B2*F1 (copied down to row 3 becomes =B3*F2) → The rate cell F1 has no $ signs, so it is relative. When the formula is copied down, F1 shifts to F2 and the rate is wrong. Fix it by writing $F$1.
=B2/B$10 copied across to column C → Only the row number is locked, so the column letter changes when copied across. Both parts need locking, so it should be $B$10.

Key Term

$B$1
An absolute cell reference. The $ before the column letter and row number locks the cell, so the reference does not change when the formula is copied. Press F4 to add the $ signs.

Fill in the Blanks

1. =A2*$B$1
Hint: Type the reference to the fixed rate cell, locked on both the column and the row.
2. Total is in B8. Percent in C2: =B2/$B$8
Hint: Type the reference to the total cell so it stays locked when the formula is copied down.
3. Select B1 and press F4 to add $ signs
Hint: Type the name of the keyboard shortcut key that adds the $ signs.
4. C2: =A2*$B$1 C3: =A3*$B$1
Hint: Type the row number that stays locked when the formula is copied down.
5. A2 is relative, $B$1 is absolute
Hint: Type the word for a reference that does not change when it is copied.

Spot the Error

Question 1
A =B2*E1
B =B3*$E$1
C =B4*$E$1
Line A — The rate cell is not locked. When the formula is copied down, E1 becomes E2, E3 and so on. It should be =B2*$E$1 so the rate stays in E1.
Question 2
A =C2*$F1
B =C3*$F$1
C =C4*$F$1
Line A — Only the column is locked. The row number is not, so F1 changes to F2 when copied down. It should be =C2*$F$1.
Question 3
A E1: 0.9 (discount)
B D2: =B2*$E$2
C D3: =B3*$E$1
Line B — The $ signs lock the wrong cell. The discount is in E1, not E2. It should be =B2*$E$1.
Question 4
A Click D2 and type =B2*E1
B Select E1 in the formula
C Press F2 to make it $E$1
D Press Enter and copy down
Line C — F2 edits a cell. It does not add $ signs. Press F4 to turn E1 into $E$1.
Question 5
A B5: =SUM(B2:B4)
B C2: =B2/$B$5
C C3: =B3/$B$5
D C4: =B4/B5
Line D — The total cell B5 is not locked, so it shifts when the formula is copied. It should be =B4/$B$5 like the lines above.

Student with a class code? Join your class →