Control what goes into your spreadsheet — dropdown lists, number limits, date ranges, and custom rules.
Data validation restricts what someone can type into a cell. Instead of hoping people enter data correctly, you define the rules — a dropdown of valid options, a number range, a date limit — and Excel enforces it automatically.
It's most valuable when a spreadsheet is shared. Without validation, one person types "North", another types "north ", a third types "NORTH", and your SUMIF that looks for "North" misses two of the three rows. Validation forces consistency at the point of entry.
Can be a single cell, a column, or a range.
The Data Validation dialog opens with three tabs: Settings, Input Message, Error Alert.
In the Source box, either type your options separated by commas (North,South,East,West) or click the range selector and highlight a column of options elsewhere in the sheet.
A dropdown arrow appears in the cell. Clicking it shows only the options you defined — nothing else can be entered.
If your list of options is in a named range or table, point Source at it: =ValidRegions or =Table1[Region]. Add a new item to the source range and it appears in the dropdown automatically — no editing the validation rule.
If your valid options come from real data, combine UNIQUE with a named range: =UNIQUE(RawData[Region]) as your source. The dropdown updates itself whenever new regions appear in the data.
Under Allow, choose Whole number, Decimal, or Date. Then set the condition (between, greater than, etc.) and the boundaries.
Example — allow only whole numbers between 1 and 100:
Anyone who types 150, or "n/a", or leaves the cell blank (unless you also check "Ignore blank") will get an error message.
Input Message (second tab) — shows a tooltip when the user clicks the cell, before they type anything. Use it to explain what goes here: "Enter the region: North, South, East, or West."
Error Alert (third tab) — controls what happens when someone enters invalid data. Three styles:
For data integrity, use Stop. For advisory warnings, use Warning.
The most flexible option. Under Allow, choose Custom, then write any formula that returns TRUE (valid) or FALSE (invalid).
Allow only entries that start with "INV-" (for invoice numbers):
=LEFT(A2,4)="INV-"Allow only unique values — reject duplicates:
=COUNTIF($A$2:$A$100,A2)=1Data validation only checks new entries. If cells already contain invalid data before you apply the rule, Excel won't flag them — it only catches future input. Use Data → Data Validation → Circle Invalid Data to highlight existing violations.
ExcelPro has 700+ real exercises across 7 career tracks — type actual formulas, get instant feedback.
Start practising free →