← VCE Spreadsheets, SQL & Visualisation

Number range validation in a spreadsheet

A rule that only lets a cell accept a number between a minimum and maximum, like age 11 to 14. Numbers outside the range are rejected. Use Decimal for numbers with parts.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 30, 7-8 Digital Technologies: Acquiring, storing and validating data — Module 13, ACARA Digital Technologies Years 7–8 — Acquiring and Storing Data — Module 69, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 68

Understanding Number range validation in a spreadsheet in VCE Spreadsheets, SQL & Visualisation

What it does: Ages are whole numbers, so Whole number with a minimum of 11 and a maximum of 14 fits. 13 is between 11 and 14, so it is accepted. 15 is above the maximum of 14, so it is rejected. Heights have parts after the point, so use Decimal. A whole number rule would reject 1.52. The minimum is the smallest allowed value and the maximum is the largest. Swapping them breaks the rule. Range validation only checks what is typed in. It does not calculate anything, but it helps catch typing mistakes.

Common mistakes: 'not between' would reject ages 11 to 14 and accept everything else. It should be 'Data: between' so only numbers from the minimum to the maximum are allowed. Age in years is a whole number, so the rule should be 'Allow: Whole number'. Decimal would let in values like 12.5.

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 Number range 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 spreadsheet cell for student age uses 'Whole number between' with minimum 11 and maximum 14. Which entry is accepted?
1315
Matches
Whole number between 14 and 11
↓
The minimum and maximum are the wrong way around. The minimum (11) must come first and the maximum (14) second.
Applying
Meanings
Term
Data > Data validation > Whole number between
Meaning
A rule that only lets a cell accept a number between a minimum and maximum, like age 11 to 14. Numbers outside the range are rejected. Use Decimal for numbers with parts.
Practice
Data > Data validation
Testing
Gaps
Data > Data validation > between
Check Answer
Spots
3Allow: Whole number
4Data: not between

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

The 'Gila Monster' badge — Patient Coder, 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 spreadsheet cell for student age uses 'Whole number between' with minimum 11 and maximum 14. Which entry is accepted?
A. 13 ✓
B. 15
13 is between 11 and 14, so it is accepted. 15 is above the maximum of 14, so it is rejected.
2. Which validation setting should you choose for a cell that holds a height like 1.52 metres?
A. Decimal between ✓
B. Whole number between
Heights have parts after the point, so use Decimal. A whole number rule would reject 1.52.
3. A cell uses 'Whole number between' 11 and 14. What happens when someone types 9?
A. It is accepted because it is a whole number
B. It is rejected because it is below the minimum ✓
The number must be a whole number AND inside the range. 9 is below the minimum of 11, so it is rejected.
4. Which is the correct way to set up a rule so only ages 11 to 14 are allowed?
A. Minimum: 14, Maximum: 11
B. Minimum: 11, Maximum: 14 ✓
The minimum is the smallest allowed value and the maximum is the largest. Swapping them breaks the rule.
5. True or false: a 'Whole number between' 11 and 14 rule accepts 12.5.
A. False ✓
B. True
12.5 has a part after the decimal point, so it is not a whole number. It is rejected even though it sits between 11 and 14.

Match the Pairs

Data > Data validation > Whole number between 11 and 14 → Only lets the cell accept whole numbers from 11 to 14, such as an age
Data > Data validation > Decimal between 0 and 100 → Lets the cell accept numbers with parts, like 72.5, from 0 to 100
Use this rule when a cell must hold only a whole number age from 11 to 14 → Whole number between 11 and 14
Use this rule when a cell must hold a height in metres, like 1.55, from 1 to 2 → Decimal between 1 and 2
Whole number between 14 and 11 → The minimum and maximum are the wrong way around. The minimum (11) must come first and the maximum (14) second.
Whole number between 1 and 100 (used for a test score like 85.5) → Whole number rejects values with decimal parts, so 85.5 would be refused. Decimal should be used instead.

Key Term

Data > Data validation > Whole number between
A rule that only lets a cell accept a number between a minimum and maximum, like age 11 to 14. Numbers outside the range are rejected. Use Decimal for numbers with parts.

Fill in the Blanks

1. Data > Data validation > Whole number between
Hint: Type the validation type that only accepts numbers with no decimal part.
2. Allow: Whole number Data: between Minimum: 11
Hint: Type the condition that makes the cell accept only a range of numbers.
3. Data: between Minimum: 11 Maximum: 14
Hint: Type the youngest age allowed in this range.
4. Allow: Decimal Minimum: 1.2 Maximum: 2.0
Hint: Type the validation type for numbers with parts, like a height in metres.
5. Allow: Whole number Minimum: 11 Maximum: 14
Hint: Type the oldest age allowed in this range.

Spot the Error

Question 1
A Select cells B2:B30
B Data > Data validation
C Allow: Whole number
D Data: not between
E Minimum: 11
F Maximum: 14
Line D — 'not between' would reject ages 11 to 14 and accept everything else. It should be 'Data: between' so only numbers from the minimum to the maximum are allowed.
Question 2
A Allow: Decimal
B Data: between
C Minimum: 11
D Maximum: 14
Line A — Age in years is a whole number, so the rule should be 'Allow: Whole number'. Decimal would let in values like 12.5.
Question 3
A Select cells C2:C30
B Allow: Whole number
C Data: between
D Minimum: 1.2
E Maximum: 2.0
Line B — Heights like 1.5 m have decimal parts. A Whole number rule would reject them, so the line should be 'Allow: Decimal'.
Question 4
A Select cells B2:B30
B Allow: Whole number
C Data: between
D Minimum: 14
E Maximum: 14
Line D — The minimum can't be the same as or bigger than the maximum, or no number could pass. It should be 'Minimum: 11'.
Question 5
A Select cells D2:D30
B Allow: Text length
C Data: between
D Minimum: 0
E Maximum: 100
Line B — Text length checks how many characters are typed, not the size of a number. For test scores use 'Allow: Whole number'.

Student with a class code? Join your class →