Data Validation in Excel: only allow the right values
Data validation stops people typing the wrong thing into a cell. Only whole numbers, only dates this year, only from a list — Excel refuses anything else.
What you will make
An Age column that accepts whole numbers from 0 to 120 and rejects everything else with a friendly message.
Steps
- Select the cells to protect — say B2:B100.
- Go to Data › Data Validation.
- On the Settings tab, under Allow choose Whole number.
- Data: between. Minimum
0, Maximum120. - Click the Error Alert tab. Title:
Check the age. Message:Type a whole number between 0 and 120. - Click OK.
Check it worked
Type abc in B2 and press Enter. Your message pops up and the cell stays empty. Type 34 — accepted.
Other rules you can set
| Allow | Use it for |
|---|---|
| Decimal | Prices, percentages (0 to 1) |
| List | Fixed choices — see drop down list |
| Date | Dates between two dates, or after today |
| Text length | Postcodes, codes of exactly 6 characters |
| Custom | A formula, e.g. =ISNUMBER(B2) |
Show a hint before they type
On the Input Message tab, type a short note. It appears as a tooltip when the cell is selected.
Find cells that break the rule
Old data does not get checked. Click Data Validation › Circle Invalid Data and Excel draws a red circle around every cell that fails.
Remove the rule
Select the cells, open Data Validation, click Clear All, OK.
20 years with spreadsheets and enterprise systems. Writes one short, plain-English Excel lesson a day. Got an Excel question? learnexceleasycom@gmail.com
