Setting spreadsheet data validation rules

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

2. True or false: Data Validation can show an error message when someone enters something that is not allowed.

A False

B True

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

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

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

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1Whole number between 1 and 100		AThe list items are not separated by commas, so the drop-down shows one long item instead of three choices.
2List of items: Yes,No,Maybe		BData Validation: Whole number, between, Minimum 0, Maximum 100
3Stop students entering a mark over 100 by restricting the cell to a number range		CA 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.
4Show a helpful message when someone enters something not allowed		DA drop-down list rule that lets the user pick only Yes, No or Maybe
5Allow: Whole number, between 1 and 100. Test entry: 150 is accepted		EA number range rule that only accepts whole numbers from 1 to 100 in the chosen cells
6Allow: List, Source: Red Blue Green		FError alert message: Please enter a mark from 0 to 100

Definitions

Data Validation

Fill in the Blanks

Complete each line of code by filling in the missing parts.

Criteria: ____________ between 1 and 100

Criteria: Whole number _______ 1 and 100

Rule: whole number, 1 to 100 Criteria: Whole number between 1 and ___

Allow: ____ Source: Year 9,Year 10

Tab: ___________ Title: Invalid mark Message: Enter a mark from 1 to 100

Spot the Error

Shade in the letter of the line that contains an 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

Question 2

A Select cells C2:C20

B Allow: Whole number

C Source: Yes,No,Maybe

D Show drop-down: ticked

Question 3

A Select cell D1

B Allow: Decimal

C Data: between 0 and 10

Question 4

A Select cells E2:E50

B Allow: Whole number

C Show error alert: unticked

D Error message: Use 0 to 120

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

Answer Key

Setting spreadsheet data validation rules

Section A: Quick Questions

A — Whole number between 0 and 100

B — True

A — Drop-down list with Red, Blue, Green, Yellow

B — Enter a whole number from 10 to 18

A — Data menu, then Data Validation

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

Criteria: Whole number between 1 and 100

Rule: whole number, 1 to 100
Criteria: Whole number between 1 and 100

Allow: List
Source: Year 9,Year 10

Tab: Error Alert
Title: Invalid mark
Message: Enter a mark from 1 to 100

Section E: Spot the Error

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.

B — A drop-down list needs the Allow setting to be 'List'. 'Whole number' would expect a number range, not a Source list.

A — D1 is only the heading cell. The rule must be applied to the data range D2:D40 so every entry is checked.

C — If the error alert is switched off, the custom error message never appears and bad data is accepted. It should be 'ticked'.

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.