Data Validation and Dropdown Lists - Guide consistent input
Create a controlled status list and a whole-number score rule, then understand why validation still needs an audit of imported or pasted data.
By the end, you can
- Create a list-based in-cell dropdown.
- Set a useful input message and Stop alert.
- Validate a numeric bound and recognize bypass limitations.
Before you start: Prepare the allowed values and target cells. Steps use Windows desktop Excel.
In this lesson
Guide entry at the field where errors occur
Data Validation defines an allowed input rule for selected cells. A ticket register can offer Open, Review, and Closed instead of many spelling variants; an assessment field can accept only whole numbers from 0 to 100. A dropdown guides entry, but validation is not proof that every existing or imported record is valid.
Status choices and score fields
Open| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Ticket | Status | Score | Allowed status | |
| 2 | T101 | Open | 78 | Open | |
| 3 | T102 | Review | 86 | Review | |
| 4 | T103 | Closed | 92 | Closed | |
| 5 | T104 | Open | 84 |
Create a dropdown and test a direct entry
- Select B2:B5 and choose Data > Data Validation.
- On Settings, select Allow: List. Set Source to =$E$2:$E$4 and keep In-cell dropdown selected.
- Set the intended blank policy. On Input Message, add a short instruction such as Choose the current ticket status.
- On Error Alert, enable the alert, choose Stop, and supply an explanatory message for an invalid status.
- Confirm the dialog. Open a cell's dropdown and choose Review; then test a directly typed Done value, which should be rejected by the Stop list rule.
- Select C2:C5 and create a separate Whole Number rule, Between 0 and 100. Test 85 as valid and 101 as invalid.
Accepted| A | B | C | |
|---|---|---|---|
| 1 | Proposed entry | Rule | Outcome with Stop |
| 2 | Status: Review | Member of list | Accepted |
| 3 | Status: Done | Not in list | Rejected |
| 4 | Score: 85 | Whole number from 0 to 100 | Accepted |
| 5 | Score: 101 | Outside the bound | Rejected |
Manage the source and maintain data quality
- For a short stable list, a same-sheet range is straightforward. A named range or a suitable Table-based list can make recurring source maintenance easier.
- Exclude the list heading and unintended blanks from Source. Check that new choices actually enter the referenced source range.
- Choose Date, Time, Decimal, Text Length, or Custom rules for other field types instead of putting every rule into a text list.
- Use the correct alert style: Warning or Information can allow a user to continue with an invalid directly typed entry, unlike Stop.
Common mistakes and limitations
The custom workspace does not implement these native validation or dropdown dialogs. No existing challenge is labelled as a Data Validation exercise.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.