Intermediate Excel · Interactive lesson 7 of 12
Data validation & input control
Validation stops bad data before it ever lands in the sheet — a dropdown instead of free text, a date range instead of any date, a clear message instead of a cryptic rejection.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialHow to Use Data Validation in ExcelSkill check
Try these first:
- Build a dropdown list limiting a cell to a fixed set of values.
- Restrict a cell to whole numbers or dates in a range.
- Add an input message and a custom error alert.
- Protect formula cells while leaving input cells open.
All familiar? Skip ahead to I8 — PivotTable Fundamentals.
Targeted teach
What you're learning
- Dropdown lists
- Data → Data Validation → List — type the options directly, or point at a range that holds them.
- Whole number / date rules
- Reject anything outside a range you set — a quantity under zero, or a date outside the current quarter.
- Input messages
- A small tooltip that appears the moment the cell is selected — tells someone what belongs there before they type anything.
- Error alerts
- A custom message shown after an invalid entry, in plain language instead of Excel's default wording.
- Dependent lists
- Optional extension — a second dropdown whose options change based on what was picked in the first.
- Protecting formulas
- Lock the formula cells, then Protect Sheet — input cells you've left unlocked stay editable, everything else is read-only.
Where to find it in Excel
Data›Data Tools›Data Validation
| A | B | C | D | E | F | ||
|---|---|---|---|---|---|---|---|
| 1 | Item | Qty | Category | Categories | |||
| 2 | Stapler | 2 | Office▾ | Office | |||
| 3 | Monitor | 1 | Tech | ||||
| 4 | Furniture | ||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 | |||||||
| 9 |
Swipe sideways to see the whole window.
- 1Select the input cells, then open the Data tab.
- 2Data Validation opens the rule dialog.
- 3Allow: List makes a dropdown; Whole number and Date set limits instead.
- 4Source is the list of allowed values. Input Message and Error Alert are the other two tabs.
- 5The cell now shows a dropdown arrow when selected.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided practice
Pick a department and status from the dropdowns, then try a start date outside the current quarter.
Independent variation
Validate a real intake form
Add dropdowns and range rules to a sheet you actually use to collect input — a request form, a sign-up sheet, a status tracker. Write a plain-language error message for at least one rule, then protect the formula cells so a collaborator can't overwrite them by accident.
Spot a mistake or have an idea for this lesson? Send feedback