Intermediate level · Module 2: Excel for Everyday Office Data · Lesson 3 of 4
Data Validation and Drop-down Lists
Stop errors at the point of entry with drop-down lists, input rules and helpful messages.
In this lesson you will
- Create drop-down lists for consistent entries
- Restrict entries to valid numbers and dates
- Add input and error messages
Key concepts
data validationdrop-down listinput messageerror alert
The lesson
Many spreadsheet errors begin with typing: 'Juba', 'juba' and 'Juba Town' look like three different places to Excel. Data Validation restricts what can be typed in a cell, so the data stays consistent from the start.
A List rule creates a drop-down menu from a range of allowed values, such as departments or counties. Whole number, Decimal and Date rules prevent impossible values, such as negative quantities or dates in the future.
An input message explains what to enter when the cell is selected; an error alert explains why an entry was refused. Clear messages save staff time and stop them working around the rules.
Step by step
- Create a list of departments on a separate sheet.
- Apply a List validation rule to the department column.
- Restrict the quantity column to whole numbers of 0 or more.
- Add an input message and a clear error alert, then test with wrong values.
Practice activity
Add validation to the office records workbook so departments, quantities and dates can only be entered correctly, and test it with five deliberately wrong entries.
Expected result: A workbook that refuses invalid entries with clear messages and offers drop-down lists for categories.
Check your understanding
-
Why is a drop-down list better than free typing for a 'County' column?
Show answer
It forces identical spellings, so sorting, filtering and SUMIF totals treat each county as one category.
Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.