← ACARA Digital Technologies Years 9–10

Referencing a cell on another worksheet

Pulls the value from cell B2 on the sheet named Data. Write the sheet name, then !, then the cell. Names with spaces need quotes: ='Sales Data'!B2.

Part of: ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 60

Understanding Referencing a cell on another worksheet in ACARA Digital Technologies Years 9–10

What it does: The quotes tell the spreadsheet that Sales Data is one sheet name, so it can find cell B2 on that sheet. Without the Data! prefix, the formula adds up B2:B9 on the Dashboard sheet itself, not on the Data sheet. A reference with no sheet name always points to the sheet the formula is on. To use another sheet, add its name and !. The sheet name must be followed by !, not a colon. The colon is only used inside the range B2:B9.

Common mistakes: A full stop is used instead of an exclamation mark. The sheet name and the cell must be separated by !, so it should be =Data!B3. The sheet name must come first, followed by !, then the range. It should be =SUM(Data!B2:B9).

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 Referencing a cell on another worksheet to a class →

This module's key terms are also in a printable crossword (Modules 413–424) and word search (Modules 413–451).

Take this worksheet further — free with Cloodit

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

Learning
Duo
Which formula pulls the value from cell B2 on a sheet named Data?
=Data!B2=Data.B2
Matches
=Sales Data!B2
↓
The sheet name has a space but no single quotes around it, so the formula gives an error.
Applying
Meanings
Term
=Data!B2
Meaning
Pulls the value from cell B2 on the sheet named Data. Write the sheet name, then !, then the cell. Names with spaces need quotes: ='Sales Data'!B2.
Practice
=AVERAGE(marks!B2:B9)
Testing
Gaps
=DataB2
Check Answer
Spots
1A1: =Data!B2
2A2: =Data.B3

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

The 'Dolphin' badge — Reef Streak, 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. Which formula pulls the value from cell B2 on a sheet named Data?
A. =Data.B2
B. =Data!B2 ✓
The sheet name comes first, then an exclamation mark !, then the cell address. A full stop is not used.
2. Which symbol separates the sheet name from the cell address in a reference?
A. : (colon)
B. ! (exclamation mark) ✓
The ! joins the sheet name to the cell, as in =Data!B2. A colon is used for ranges such as B2:B9.
3. A sheet is named Sales Data. Which formula correctly refers to its cell B2?
A. ='Sales Data'!B2 ✓
B. =Sales Data!B2
Sheet names with spaces must be wrapped in single quotes, so Excel knows where the name starts and ends.
4. Your Dashboard sheet needs to show the total of Data!B2:B9. Which formula should go on the Dashboard?
A. =SUM(Data!B2:B9) ✓
B. =SUM(B2:B9)
Without the Data! prefix, the formula adds up B2:B9 on the Dashboard sheet itself, not on the Data sheet.
5. You type =B2 on the Dashboard sheet. Where does it take its value from?
A. Cell B2 on the Dashboard sheet ✓
B. Cell B2 on the Data sheet
A reference with no sheet name always points to the sheet the formula is on. To use another sheet, add its name and !.

Match the Pairs

=Data!B2 → Pulls the value from cell B2 on the sheet named Data
=SUM(Data!B2:B9) → Adds up the range B2:B9 on the sheet named Data
A sheet name that contains a space must be wrapped in single quotes before the exclamation mark → ='Sales Data'!B2
A Dashboard sheet cell shows the value of cell C5 on the Data sheet → =Data!C5
=Sales Data!B2 → The sheet name has a space but no single quotes around it, so the formula gives an error.
=Data.B2 → A full stop is used instead of an exclamation mark between the sheet name and the cell.

Key Term

=Data!B2
Pulls the value from cell B2 on the sheet named Data. Write the sheet name, then !, then the cell. Names with spaces need quotes: ='Sales Data'!B2.

Fill in the Blanks

1. =Data!B2
Hint: Type the symbol that separates the sheet name from the cell address.
2. =Data!B2+Data!B3
Hint: Type the sheet name so this cell comes from the same sheet as the second reference.
3. =AVERAGE('Sales Data'!B2:B9)
Hint: Type the function that finds the mean of a range on a sheet whose name has a space and is in quotes.
4. =SUM(Data!B2:B9)
Hint: Type the function name that adds up a range of cells on another sheet.
5. =SUM(Data!B2:B9)
Hint: Type the symbol that joins the sheet name to the range, so a Dashboard can read the Data sheet.

Spot the Error

Question 1
A A1: =Data!B2
B A2: =Data.B3
C A3: =Data!B4
Line B — A full stop is used instead of an exclamation mark. The sheet name and the cell must be separated by !, so it should be =Data!B3.
Question 2
A Dashboard sheet, column B
B B1: =Data!B2
C B2: =Data!B3
D B3: =SUM(B2:B9!Data)
Line D — The sheet name must come first, followed by !, then the range. It should be =SUM(Data!B2:B9).
Question 3
A A1: ='Sales Data'!B2
B A2: ='Sales Data'!B3
C A3: =Sales Data!B4
Line C — A sheet name containing a space needs single quotes around it. It should be ='Sales Data'!B4.
Question 4
A Dashboard sheet
B B1: =SUM(Data!B2:B9)
C B2: =AVERAGE(Data,B2:B9)
D B3: =MAX(Data!B2:B9)
Line C — A comma is used where the exclamation mark should be. Without !, the formula does not point to the Data sheet. It should be =AVERAGE(Data!B2:B9).
Question 5
A A1: ='Term 1'!C2
B A2: ='Term 1'!C3
C A3: ="Term 1"!C4
Line C — Sheet names are wrapped in single quotes, not double quotes. It should be ='Term 1'!C4.

Student with a class code? Join your class →