Data Validation
Stop bad data before it gets entered, with dropdown lists and input rules.
Compatibility: All Excel versions
What You'll Learn
- How to create a dropdown list using Data Validation
- How to restrict a cell to a number or date within a specific range
- How to find existing data that doesn't follow a validation rule
Data Validation (Data tab > Data Validation) restricts what can be typed into a cell — useful for preventing typos, out-of-range numbers, or inconsistent category names in a shared spreadsheet.
Dropdown Lists
The most common use: choose "List" as the validation type and provide a set of allowed values (either typed directly, or referencing a range of cells) — this turns the cell into a dropdown, so anyone entering data picks from your predefined options instead of typing something inconsistent like "Yes", "yes", and "Y" in different rows.
Numeric and Date Rules
You can also restrict a cell to whole numbers within a range, a decimal within a range, or a date within a range — with a custom error message that appears if someone tries to enter something outside those bounds.
Circling Invalid Data
If you apply validation to cells that already contain data, use Data > Data Validation > Circle Invalid Data to visually flag any existing entries that don't follow the new rule.
Common Mistakes
- Applying validation only to some of the cells that need it, leaving gaps where bad data can still get in
- Not testing the dropdown after setting it up to confirm the list actually works as expected
Practice Exercise
Create a column for "Status" and apply a dropdown with three options: Not Started, In Progress, Complete. Try typing something outside the list and confirm Excel blocks it.
Key Takeaways
- Data Validation restricts what can be entered into a cell
- A List validation type turns a cell into a dropdown
- Circle Invalid Data flags existing entries that break a newly-applied rule