Module 4 · Tables and rules that keep a sheet correct Lesson 11 of 15

Data validation and drop-downs

The concept

A validation error at the moment of typing is worth an hour of cleaning later, and the input-message tab lets you write the rule in words where the person can see it.

Keep validating in the import step too if the data arrives from elsewhere - cell validation is a courtesy to the typist, not a guard against bad data.

SpreadsheetCopy

Data -> Data Validation
  Allow: Whole number   Minimum: 0         -> refuses negative amounts
  Allow: List           Source: =Categories -> a drop-down
  Allow: Date           Start: =TODAY()     -> no past dates

Custom: =AND(LEN(A2)=10, ISNUMBER(A2*1))    -> a 10-digit mobile number

Worked example

Data -> Data Validation -> List -> the five city names gives a drop-down; the same dialog refuses a past date with a simple rule.

Your practice task

Add a drop-down of five categories and a rule that refuses a negative amount, on one data-entry sheet.

Do this before moving on. Reading a solution teaches the shape of it; typing it teaches you.

Key takeaway

A validation error at the moment of typing is worth an hour of cleaning later, and the input-message tab lets you write the rule in words where the person can see it.

Almost there

15 lessons left of 15

Finish every lesson to unlock the certificate. Each one ends in a practice task — do them and the final project brief writes itself.

Next recommended course Spoken English & Interview Communication