← ACARA Digital Technologies Years 9–10

Named ranges

A readable name given to a cell or range, like Selected_Year, which formulas and charts can use instead of cell addresses such as B2:B30.

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

Understanding Named ranges in ACARA Digital Technologies Years 9–10

What it does: Typing a name into the Name Box is the quickest way to create a named range from the current selection. The Define Name dialog lets you choose the name and the exact cell or range it refers to. Select the cell, click the Name Box, type the name and press Enter. A name is typed without quotes. Quotes would make it text, so the formula would not use your range. Names cannot contain spaces. Use an underscore instead, like Selected_Year. A1 is a cell address, so Excel will not accept it as a name. A clear name says what the cell holds.

Common mistakes: A name typed in the Name Box is only saved when you press Enter. Pressing Tab does not create the named range. It should be: Type Sales and press Enter. Named ranges cannot contain spaces. Use an underscore instead: Selected_Year.

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 Named ranges 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 is a named range?
B2:B30, a block of cell addressesSelected_Year, a readable name given to a cell
Matches
Name: Selected Year
↓
Names cannot contain spaces. Use an underscore instead, such as Selected_Year.
Applying
Meanings
Term
Named range
Meaning
A readable name given to a cell or range, like Selected_Year, which formulas and charts can use instead of cell addresses such as B2:B30.
Practice
=ROUND(AVERAGE(B2:B9),1)
Testing
Gaps
=(MathsMarks)
Check Answer
Spots
2Click the Name Box
3Type Sales and press Tab

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

The 'Zebra' badge — Pattern Matcher, 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 is a named range?
A. B2:B30, a block of cell addresses
B. Selected_Year, a readable name given to a cell ✓
A named range replaces cell addresses like B2:B30 with a readable name such as Selected_Year.
2. Where do you type a name to give a selected cell its name quickly?
A. The Name Box, left of the formula bar ✓
B. The sheet tab at the bottom
Select the cell, click the Name Box, type the name and press Enter.
3. Which formula uses a named range called Sales?
A. =SUM(Sales) ✓
B. =SUM("Sales")
A name is typed without quotes. Quotes would make it text, so the formula would not use your range.
4. Why use a name like Selected_Year instead of a cell address like $B$2?
A. The formula is easier to read and understand ✓
B. The formula will always calculate faster
Names make formulas readable, so you can see what the formula uses without checking the sheet.
5. Which is a valid name for a named range?
A. Selected Year
B. Selected_Year ✓
Names cannot contain spaces. Use an underscore instead, like Selected_Year.

Match the Pairs

=AVERAGE(Maths_Marks) → Finds the mean of a range that has been given the name Maths_Marks, instead of typing cell addresses
=SUM(Sales)*Tax_Rate → Multiplies the total of the range named Sales by the value in the cell named Tax_Rate
The Name Box, next to the formula bar, is where you type a name for the cells you have selected, then press Enter → Select B2:B9, click the Name Box, type Maths_Marks, press Enter
Define Name on the Formulas tab creates a named range and lets you set what cells it refers to → Formulas > Define Name > Name: Selected_Year > Refers to: =Sheet1!$B$2
Name: Selected Year → Names cannot contain spaces. Use an underscore instead, such as Selected_Year.
=SUM("Maths_Marks") → The name is in quotation marks, so it is treated as text. Type the name without quotes: =SUM(Maths_Marks).

Key Term

Named range
A readable name given to a cell or range, like Selected_Year, which formulas and charts can use instead of cell addresses such as B2:B30.

Fill in the Blanks

1. =AVERAGE(MathsMarks)
Hint: Type the function that finds the mean of the values in the named range.
2. Formulas > Define Name
Hint: Type the command on the Formulas tab that creates a name for the selected cells.
3. Name Box: RainDays =COUNTIF(RainDays,">0")
Hint: Type the named range you created in the Name Box instead of cell addresses.
4. Select B2:B30, type Prices in the Name Box
Hint: Type the name of the small box left of the formula bar where you type a range's name.
5. Named ranges: Region, Sales =SUMIF(Region,"North",Sales)
Hint: Type the named range that holds the values to be added up.

Spot the Error

Question 1
A Select cells B2:B30
B Click the Name Box
C Type Sales and press Tab
Line C — A name typed in the Name Box is only saved when you press Enter. Pressing Tab does not create the named range. It should be: Type Sales and press Enter.
Question 2
A Name: Selected Year
B Refers to: =Sheet1!$B$2
C Use it: =Selected_Year+1
Line A — Named ranges cannot contain spaces. Use an underscore instead: Selected_Year.
Question 3
A Cells B2:B30 are named Marks
B In cell E2 type a formula
C =AVERAGE("Marks")
Line C — Quote marks turn Marks into plain text, so the formula does not use the named range. Write the name without quotes: =AVERAGE(Marks).
Question 4
A Insert tab > Define Name
B Name: Prices
C Refers to: =Shop!$C$2:$C$9
Line A — Define Name is on the Formulas tab, not the Insert tab. It should be: Formulas tab > Define Name.
Question 5
A Name: Max_Temp
B Refers to: =Weather!$B$2:$B$8
C In cell F2 type:
D =MAX(MaxTemp)
Line D — The name must be spelled exactly as defined. MaxTemp does not exist, so Excel shows #NAME?. It should be =MAX(Max_Temp).

Student with a class code? Join your class →