← ACARA Digital Technologies Years 9–10

A drop-down list as a chart selector

A drop-down in one cell, made with Data Validation, that lets the user pick an item from a range; formulas read that cell so the chart shows only the chosen item.

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

Understanding A drop-down list as a chart selector in ACARA Digital Technologies Years 9–10

What it does: After the drop-down cell is named Selected_Item in the Name Box, formulas can use that name instead of a cell like H1, so they show what the cell is for. Pointing to the input cell ($B$1) means the result changes when the user picks a new item. Typing "Maths" into the formula fixes it forever. Labels do not change how the chart works. They help the user understand the selector, which makes the visualisation clearer. The Source must point to the range that holds the items. SUM gives one number, not a list of choices. The fix is to type =F2:F6 (or =$F$2:$F$6) so the drop-down list reads the cells in the range.

Common mistakes: A1 holds only the label text. The formula must read the selector cell B1, so the chart follows the chosen item. It should be =XLOOKUP(B1,A4:A9,B4:B9). A whole number rule only accepts numbers and gives no drop-down. To get a drop-down of items from a range, Allow must be List.

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 A drop-down list as a chart selector 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 Data Validation setting creates a drop-down list of items taken from a range of cells?
Allow: Whole number, with the Source set to the range of itemsAllow: List, with the Source set to the range of items
Matches
Allow: List Source: F2:F6
↓
The source is missing the = sign, so Excel treats F2:F6 as typed text and the drop-down shows only one item called F2:F6 instead of the items in the range.
Applying
Meanings
Term
Data Validation List
Meaning
A drop-down in one cell, made with Data Validation, that lets the user pick an item from a range; formulas read that cell so the chart shows only the chosen item.
Practice
=INDEX(B2:B9,MATCH(A2,A2:A9,0))
Testing
Gaps
Allow:
Check Answer
Spots
3Source: =$A$4:$A$9
4E4: =XLOOKUP(A1,A4:A9,B4:B9)

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

The 'Ostrich' badge — Full Sprint, 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 Data Validation setting creates a drop-down list of items taken from a range of cells?
A. Allow: Whole number, with the Source set to the range of items
B. Allow: List, with the Source set to the range of items ✓
Choosing Allow: List makes the cell show a drop-down arrow. The Source tells it which cells hold the items to pick from.
2. What does the drop-down cell do in a chart selector?
A. It stores the item the user picks, so formulas can read it and the chart can show that item ✓
B. It draws the chart itself, so no formulas are needed
The drop-down cell only stores the choice. Other formulas read that cell, and the chart is fed by those formulas.
3. Which is the better way to use the selector cell in a formula that feeds the chart?
A. =XLOOKUP($B$1, A5:A10, B5:B10) ✓
B. =XLOOKUP("Maths", A5:A10, B5:B10)
Pointing to the input cell ($B$1) means the result changes when the user picks a new item. Typing "Maths" into the formula fixes it forever.
4. The user picks 'Science' in the drop-down but the chart still shows Maths. What is the most likely cause?
A. Drop-down lists can only be used with text, never with charts
B. The chart's data comes from cells that do not read the selector cell ✓
A drop-down only changes a cell value. The chart updates only if its source cells have formulas that use that selector cell.
5. Why give the selector cell a name like SelectedSubject?
A. Formulas such as =XLOOKUP(SelectedSubject, ...) are easier to read and check ✓
B. A cell must be named before a drop-down can be added to it
A named input cell is optional but helpful. It makes formulas clearer and stops mistakes with cell addresses.

Match the Pairs

=$F$2:$F$6 → The Data Validation List source: the range of items that appear in the drop-down
=INDEX(B2:E2,MATCH(H1,B1:E1,0)) → Reads the item chosen in the drop-down cell H1 and returns its value for the chart to use
Naming the input cell makes a formula easier to read than using a cell address → =XLOOKUP(Selected_Item,A2:A9,C2:C9)
A label next to the selector tells the user what to choose → G1: Choose a subject:
Allow: List Source: F2:F6 → The source is missing the = sign, so Excel treats F2:F6 as typed text and the drop-down shows only one item called F2:F6 instead of the items in the range.
Chart data: =Sheet1!$B$2:$B$9 (the full table) → The chart reads the original table, not the formula cells that depend on the drop-down, so choosing a different item changes nothing on the chart.

Key Term

Data Validation List
A drop-down in one cell, made with Data Validation, that lets the user pick an item from a range; formulas read that cell so the chart shows only the chosen item.

Fill in the Blanks

1. Data Validation Allow: List Source: =$E$2:$E$6
Hint: Type the validation type that makes a drop-down of items.
2. Name Box for B1: Selected =INDEX(B2:B6,MATCH(Selected,A2:A6,0))
Hint: Type the name given to the input cell in the Name Box.
3. A1: Choose a region B1: drop-down list C1: ="Sales: "&B1
Hint: Type the cell reference of the drop-down selector.
4. =INDEX(B2:B6,MATCH(B1,A2:A6,0))
Hint: Type the function that finds the position of the chosen item.
5. =INDEX(B2:B6,MATCH(B1,A2:A6,0))
Hint: Type the match type number that finds an exact match.

Spot the Error

Question 1
A A1: Choose a student
B B1: Data Validation > List
C Source: =$A$4:$A$9
D E4: =XLOOKUP(A1,A4:A9,B4:B9)
Line D — A1 holds only the label text. The formula must read the selector cell B1, so the chart follows the chosen item. It should be =XLOOKUP(B1,A4:A9,B4:B9).
Question 2
A A1: Pick a month
B B1: Data Validation
C Allow: Whole number
D Source: =$A$4:$A$9
Line C — A whole number rule only accepts numbers and gives no drop-down. To get a drop-down of items from a range, Allow must be List.
Question 3
A Select A4:A9
B Data > Data Validation
C Allow: List
D Source: =$A$4:$A$9
Line A — The drop-down must go in the single selector cell B1. Selecting the list range A4:A9 would put a drop-down in every cell of the list itself. It should be Select B1.
Question 4
A Select B1
B Name Box: Pick, press Enter
C E4: =XLOOKUP(Pik,A4:A9,B4:B9)
D Chart series values: E4
Line C — The named input cell is called Pick, but the formula uses Pik, which does not exist and gives #NAME?. It should be =XLOOKUP(Pick,A4:A9,B4:B9).
Question 5
A Select B1
B Type in B1: Choose a student
C Data Validation > List
D Source: =$A$4:$A$9
Line B — The label belongs in A1, next to the selector. Typing it in B1 overwrites the selector cell. It should be Type in A1: Choose a student.

Student with a class code? Join your class →