← VCE Spreadsheets, SQL & Visualisation

Drop-down list validation in a spreadsheet

A rule that lets a cell accept only items from a set list, shown as a drop-down. Items can be typed in or taken from a range of cells, which keeps spelling consistent.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 31, 7-8 Digital Technologies: Acquiring, storing and validating data — Module 14, ACARA Digital Technologies Years 7–8 — Acquiring and Storing Data — Module 70, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 69, ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 61

Understanding Drop-down list validation in a spreadsheet in VCE Spreadsheets, SQL & Visualisation

What it does: A typed list of items is separated by commas. Each item becomes one choice in the drop-down. List validation lets the user pick only from a set of choices. Number range validation is for numbers between a minimum and a maximum. The list stops mistakes before they happen. Without it, someone might type Melborne or melbourne, which a filter would treat as different. If the rule points to a fixed range, a new item outside that range will not appear. Extend the range to include it. Each choice should be listed only once with one agreed spelling, such as Year 7.

Common mistakes: A drop-down needs the criteria to be a list. 'Number between' is a number range rule and gives no drop-down. It should be 'Criteria: List of items'. 'Text contains' only checks for some text. It does not give a set of choices. The criteria should be 'List of items' so the cell shows a drop-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 Drop-down list validation in a spreadsheet to a class →

This module's key terms are also in a printable crossword (Modules 21–34) 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
A teacher wants students to pick their house colour from Red, Blue, Green or Yellow in a cell. Which validation rule fits best?
List validation, so the cell shows a drop-down of the four coloursNumber range validation, so the cell only accepts numbers
Matches
Red Blue Green Yellow
↓
The typed items are separated by spaces, not commas, so the spreadsheet reads them as one choice instead of four.
Applying
Meanings
Term
List validation
Meaning
A rule that lets a cell accept only items from a set list, shown as a drop-down. Items can be typed in or taken from a range of cells, which keeps spelling consistent.
Practice
=COUNTIF($E$2:$E$5,B2)
Testing
Gaps
Criteria: of items
Check Answer
Spots
2Open Data > Data validation
3Criteria: Number between

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

The 'Moon Jellyfish' badge — Orbital 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. A teacher wants students to pick their house colour from Red, Blue, Green or Yellow in a cell. Which validation rule fits best?
A. List validation, so the cell shows a drop-down of the four colours ✓
B. Number range validation, so the cell only accepts numbers
List validation lets the user pick only from a set of choices. Number range validation is for numbers between a minimum and a maximum.
2. Which statement about a drop-down list in a cell is true?
A. The user picks one item from the list instead of typing it ✓
B. The user can type any word they like and it is always accepted
A list rule only accepts items from the set list, so random words are rejected. This stops mistakes.
3. You are typing the allowed items straight into the validation box. Which way is correct?
A. Yes.No.Maybe
B. Yes,No,Maybe ✓
When you type the items in, you separate them with commas. The spreadsheet then turns each one into a drop-down choice.
4. Your list of allowed items is written in cells E2:E5. How do you tell the validation rule to use them?
A. Choose list from a range and select E2:E5 ✓
B. Type E2:E5 as one item in the typed list
With list from a range, the rule reads its items from the cells. Typing E2:E5 as an item would just make a choice that says E2:E5.
5. Why does a drop-down list help keep spelling consistent?
A. The spreadsheet fixes spelling mistakes after they are typed
B. Everyone picks the same spelling, such as Melbourne, every time ✓
The list stops mistakes before they happen. Without it, someone might type Melborne or melbourne, which a filter would treat as different.

Match the Pairs

Criteria: List of items → The data validation setting that makes a cell show a drop-down of set choices
=Sheet2!$A$2:$A$6 → Takes the drop-down choices from a range of cells instead of typing them in
Type the allowed items straight into the validation box, separated by commas → Red,Blue,Green,Yellow
Use a list so every student picks the same spelling, such as Year 7, rather than typing Yr7 or year seven → Year 7,Year 8
Red Blue Green Yellow → The typed items are separated by spaces, not commas, so the spreadsheet reads them as one choice instead of four.
Drop-down list: Yr7, year 7, Year Seven → The list holds three different spellings of the same thing, so the data will be inconsistent and hard to sort or filter.

Key Term

List validation
A rule that lets a cell accept only items from a set list, shown as a drop-down. Items can be typed in or taken from a range of cells, which keeps spelling consistent.

Fill in the Blanks

1. Criteria: List of items
Hint: Type the validation criteria name that gives a drop-down of set choices.
2. Option: Show drop-down list in cell
Hint: Type the word for the menu that opens in the cell so the user can pick an item.
3. A typed list keeps spelling the same
Hint: Type the word for writing words correctly the same way every time, which a list helps keep consistent.
4. Source range: =Lists!A2:A6
Hint: Type the operator that joins the first and last cells of a range.
5. On invalid data: Reject the input
Hint: Type the action that stops spellings not on the list from being entered.

Spot the Error

Question 1
A Select cells C2:C30
B Open Data > Data validation
C Criteria: Number between
D Items: Yes,No,Maybe
Line C — A drop-down needs the criteria to be a list. 'Number between' is a number range rule and gives no drop-down. It should be 'Criteria: List of items'.
Question 2
A Select cells D2:D25
B Criteria: Text contains
C Items: Small,Medium,Large
D Show drop-down: Yes
Line B — 'Text contains' only checks for some text. It does not give a set of choices. The criteria should be 'List of items' so the cell shows a drop-down.
Question 3
A Criteria: List from a range
B Range: $F$2-$F$6
C Show drop-down: Yes
Line B — A range of cells uses a colon between the first and last cell. A hyphen is not a valid range. It should be 'Range: $F$2:$F$6'.
Question 4
A Select cells E2:E20
B Criteria: List of items
C Items: Mon Tue Wed Thu Fri
D Show drop-down: Yes
Line C — Typed items must be separated by commas. Without commas the spreadsheet reads one long item instead of five choices. It should be 'Items: Mon,Tue,Wed,Thu,Fri'.
Question 5
A Pets are typed in H2:H5
B Range: Dog,Cat,Fish
C Criteria: List from a range
D Show drop-down: Yes
Line B — When the list comes from a range of cells, the Range box needs cell addresses, not typed words. It should be 'Range: $H$2:$H$5'.

Student with a class code? Join your class →