Data validation
Guide data entry with clear lists, numeric limits and useful error messages.
By the end, you’ll be able to:
- Use numeric and list rules for controlled input
- Test boundaries and fractional values
- Understand why validation does not guarantee clean data
Define what a valid input means
An order quantity must be a whole number from 1 through 100. A status must come from Pending, Approved or Rejected. These are input contracts: a colleague should know the allowed values before receiving an error.
Whole number is different from Decimal. A rule that checks only whether a value lies between 1 and 100 would accept 25.5, even though the quantity policy does not. Choose a rule type that reflects the meaning.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Input | Quantity | Status | Allowed statuses |
| 2 | Order A | 25 | Pending | Pending |
| 3 | Order B | 100 | Approved | Approved |
| 4 | Order C | 0 | Rejected | Rejected |
Create a numeric rule in Excel
- Select B2:B4 and open Data → Data Validation.
- Choose Whole number and Between.
- Set Minimum to 1 and Maximum to 100.
- Add an input message, for example “Enter 1–100 whole units.”
- Choose a Stop error alert and a useful correction message.
Test 0, 1, 100, 101 and 25.5. Review the Ignore blank setting and decide whether blanks are acceptable for this workflow. A separate completion check may still be necessary.
Worked example: a controlled status list
The allowed labels are in D2:D4. Select C2:C4, choose List validation and set the source to =$D$2:$D$4. Enable the in-cell dropdown. Keep the source range distinct from the entry cells so users are not overwriting the list itself.
Approved is allowed; Approvd is not. A dropdown helps people use consistent spellings. In a maintained report, place source lists in a clearly identified reference area and verify validation extends when new entry rows are added.
Validation is guidance, not proof of data quality
Existing invalid values can remain after a rule is applied. Some paste, fill, formula or import workflows can bypass ordinary validation alerts or overwrite validation. Inspect imported and pasted data independently and review rule coverage.
In desktop versions with the option available, Circle Invalid Data can help locate values that violate rules. Validation is also not file security: it does not control who can access the workbook or guarantee every process follows your business policy.
Separate the numeric test from Excel’s entry control
The formula exercise below checks the inclusive numeric interval using AND; it intentionally does not check integer-ness. The knowledge tasks ask you to choose the whole-number control and test its edges. Build the actual dropdown and validation rules in the downloadable workbook.
Data validation is available in current web Excel and desktop editions, with platform differences in editing options. The website does not pretend its answer field is Excel’s native validation dialog.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 4 exercises complete
The bounded practice checker accepts equivalent supported formulas, lowercase function names and text labels, extra spaces, and comma or semicolon separators. Array tasks check the complete result shape and values. It is a teaching tool, not a complete Excel engine. See supported behavior.
Take this with you
Choose the correct validation type, explain the allowed input and test edge cases. Independently inspect existing, pasted and imported data.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Reviewed 2026-10-16.