Drop-down list validation in a spreadsheet

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

3. You are typing the allowed items straight into the validation box. Which way is correct?

A Yes.No.Maybe

B Yes,No,Maybe

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

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

Match the Pairs

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

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

Definitions

List validation

Fill in the Blanks

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

Criteria: ____ of items

Option: Show _________ list in cell

A typed list keeps ________ the same

Source range: =Lists!A2_A6

On invalid data: ______ the input

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A Select cells C2:C30

B Open Data > Data validation

C Criteria: Number between

D Items: Yes,No,Maybe

Question 2

A Select cells D2:D25

B Criteria: Text contains

C Items: Small,Medium,Large

D Show drop-down: Yes

Question 3

A Criteria: List from a range

B Range: $F$2-$F$6

C Show drop-down: Yes

Question 4

A Select cells E2:E20

B Criteria: List of items

C Items: Mon Tue Wed Thu Fri

D Show drop-down: Yes

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

Answer Key

Drop-down list validation in a spreadsheet

Section A: Quick Questions

A — List validation, so the cell shows a drop-down of the four colours

A — The user picks one item from the list instead of typing it

B — Yes,No,Maybe

A — Choose list from a range and select E2:E5

B — Everyone picks the same spelling, such as Melbourne, every time

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

Section C: Definitions

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.

Section D: Fill in the Blanks

Criteria: List of items

Option: Show drop-down list in cell

A typed list keeps spelling the same

Source range: =Lists!A2:A6

On invalid data: Reject the input

Section E: Spot the Error

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'.

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.

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'.

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'.

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'.