← VCE Data Analytics U3O1

Setting spreadsheet data validation rules

A spreadsheet tool that limits cells to allowed entries, like a whole number from 1 to 100 or a drop-down list, and shows an error message if someone types anything else.

Part of: VCE Data Analytics - U3O1 - Data Analytics — Module 50, VCE Data Analytics - U4O1 - Development and Evaluation — Module 76, 9-10 Digital Technologies: Acquiring, storing and validating data — Module 17, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 67

Understanding Setting spreadsheet data validation rules in VCE Data Analytics U3O1

What it does: A between rule with a minimum and a maximum limits entries to the allowed range. A custom error message appears when an entry breaks the Data Validation rule and tells the user how to fix it. The rule applies to the cells you have selected, so choose the range first, such as B2:B30, and then set the rule. The rule needs a whole number, so a decimal fails even though it sits inside the range. Drop-down lists keep entries consistent, such as always Year 9 instead of yr9 or Year Nine, which makes later sorting and filtering more reliable. The List of items criteria creates a drop-down menu, so any other entry is rejected.

Common mistakes: The minimum must come first and the maximum second. The rule should be 'between 1 and 100'. With the numbers reversed, no mark can pass. A drop-down list needs the Allow setting to be 'List'. 'Whole number' would expect a number range, not a Source 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 Setting spreadsheet data validation rules to a class →

This module's key terms are also in a printable crossword (Modules 50–91) and word search (Modules 17–91).

Take this worksheet further — free with Cloodit

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

Learning
Duo
Which Data Validation rule would stop someone typing a test mark of 150 into a cell that must hold a mark from 0 to 100?
Drop-down list of days of the weekWhole number between 0 and 100
Matches
Allow: Whole number, between 1 and 100. Test entry: 150 is accepted
↓
A number range rule should reject 150 because it is above the maximum of 100. The rule was probably not applied to the cell, or the error alert is turned off.
Applying
Meanings
Term
Data Validation
Meaning
A spreadsheet tool that limits cells to allowed entries, like a whole number from 1 to 100 or a drop-down list, and shows an error message if someone types anything else.
Practice
Range: B2:B30
Testing
Gaps
Criteria: between 1 and 100
Check Answer
Spots
2Allow: Whole number
3Data: between 100 and 1

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

The 'Nautilus' badge — Default Expert, 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 rule would stop someone typing a test mark of 150 into a cell that must hold a mark from 0 to 100?
A. Whole number between 0 and 100 ✓
B. Drop-down list of days of the week
A number range rule only accepts whole numbers inside the limits you set, so 150 is rejected and the error message appears.
2. True or false: Data Validation can show an error message when someone enters something that is not allowed.
A. False
B. True ✓
Data Validation checks each entry against the rule and shows an error message if it breaks the rule. You can even write your own message.
3. You want students to pick their house colour from Red, Blue, Green or Yellow, with no other answers allowed. Which rule fits best?
A. Drop-down list with Red, Blue, Green, Yellow ✓
B. Whole number between 1 and 4
A drop-down list rule limits a cell to a set of allowed choices, so people pick from the list instead of typing their own spelling.
4. Which custom error message is more helpful for a cell that only accepts ages from 10 to 18?
A. Error!
B. Enter a whole number from 10 to 18 ✓
A good custom error message tells the user what is allowed so they can fix the entry straight away.
5. Where do you find the tool to set a validation rule in most spreadsheets?
A. Data menu, then Data Validation ✓
B. Insert menu, then Chart
Data Validation is found in the Data menu. You select the cells first, then choose the rule.

Match the Pairs

Whole number between 1 and 100 → A number range rule that only accepts whole numbers from 1 to 100 in the chosen cells
List of items: Yes,No,Maybe → A drop-down list rule that lets the user pick only Yes, No or Maybe
Stop students entering a mark over 100 by restricting the cell to a number range → Data Validation: Whole number, between, Minimum 0, Maximum 100
Show a helpful message when someone enters something not allowed → Error alert message: Please enter a mark from 0 to 100
Allow: Whole number, between 1 and 100. Test entry: 150 is accepted → A number range rule should reject 150 because it is above the maximum of 100. The rule was probably not applied to the cell, or the error alert is turned off.
Allow: List, Source: Red Blue Green → The list items are not separated by commas, so the drop-down shows one long item instead of three choices.

Key Term

Data Validation
A spreadsheet tool that limits cells to allowed entries, like a whole number from 1 to 100 or a drop-down list, and shows an error message if someone types anything else.

Fill in the Blanks

1. Criteria: Whole number between 1 and 100
Hint: Type the kind of data the rule allows, so that decimals and text are rejected.
2. Criteria: Whole number between 1 and 100
Hint: Type the operator word that sets both a minimum and a maximum.
3. Rule: whole number, 1 to 100 Criteria: Whole number between 1 and 100
Hint: Type the maximum value the rule allows.
4. Allow: List Source: Year 9,Year 10
Hint: Type the Allow option that creates a drop-down menu of choices.
5. Tab: Error Alert Title: Invalid mark Message: Enter a mark from 1 to 100
Hint: Type the name of the tab where a custom error message is written.

Spot the Error

Question 1
A Select cells B2:B30
B Allow: Whole number
C Data: between 100 and 1
D Error message: Enter 1 to 100
Line C — The minimum must come first and the maximum second. The rule should be 'between 1 and 100'. With the numbers reversed, no mark can pass.
Question 2
A Select cells C2:C20
B Allow: Whole number
C Source: Yes,No,Maybe
D Show drop-down: ticked
Line B — A drop-down list needs the Allow setting to be 'List'. 'Whole number' would expect a number range, not a Source list.
Question 3
A Select cell D1
B Allow: Decimal
C Data: between 0 and 10
Line A — D1 is only the heading cell. The rule must be applied to the data range D2:D40 so every entry is checked.
Question 4
A Select cells E2:E50
B Allow: Whole number
C Show error alert: unticked
D Error message: Use 0 to 120
Line C — If the error alert is switched off, the custom error message never appears and bad data is accepted. It should be 'ticked'.
Question 5
A Select cells F2:F25
B Allow: List
C Source: Red,Blue,Green
D Error style: Information
E Error message: Pick a colour
Line D — The Information style only shows a notice and still lets the user enter anything. It should be 'Stop' so only list items are allowed.

Student with a class code? Join your class →