Data validation in Excel controls what a cell will accept. Select the cell or range, choose Data > Data Validation, and on the Settings tab pick an Allow rule: a whole number inside a range, a value from a list, a date, a text length, or a Custom formula. Excel then checks each value as it is typed against that rule. That is the core of the feature. How much it actually protects comes down to two choices the same dialog holds: the Error Alert style, because only one of them blocks a bad entry, and the fact that the check runs on typing alone.
Microsoft documents the same three-tab dialog for Excel on Windows, Mac, and the web, across Excel 2016 through 2024 and Microsoft 365. On Windows the command sits at Data > Data Tools > Data Validation. A drop-down list, the control most people picture first, is one of these rules, the List type; building one from a range, a table, or typed entries is covered in creating a drop-down list. Every other Allow rule, plus the Error Alert that decides whether a bad entry is actually stopped, works from the same dialog.
How do you add a validation rule?
Select the cells, open Data > Data Validation, and go to the Settings tab. The Allow box holds the rule types:
- Whole number and Decimal restrict the cell to a number, whole or with decimals, that meets a comparison.
- List limits the cell to a set of entries and turns it into a drop-down.
- Date and Time restrict the cell to a date or a time inside a range you set.
- Text length caps how many characters the entry can have.
- Custom accepts the entry only when a formula you write returns TRUE.
For every type except List and Custom, the Data box sets the comparison, between, greater than or equal to, less than or equal to, and the like, and you fill in a Minimum and Maximum or a single value. To cap a figure at 25, for example, set Allow to Whole number, set Data to less than or equal to, and enter 25 in the Maximum box.
A Custom rule is the open-ended one: the entry is accepted only when a formula you supply evaluates to TRUE for that cell. Microsoft’s own example holds a product code to a fixed prefix and a minimum length:
The formula can use the same logical functions you would put in an IF formula; validation reads its TRUE or FALSE instead of showing a result in the cell.
What does the Input Message tab do?
The Input Message tab shows a prompt when the cell is selected, before anyone types. Check Show input message when cell is selected, then give it a title and a short message, a note to pick a value from the list or to enter a date in the current quarter. It is guidance rather than a restriction: it appears on selection and does nothing to whatever value gets entered. The Error Alert tab is where the enforcement lives.
Stop, Warning, or Information: which alert blocks a bad entry?
The Error Alert tab decides what happens when someone types a value the rule rejects, and the Style box offers three that behave very differently:
- Stop is the only one that refuses the value. Its alert gives Retry or Cancel and will not let the invalid entry stay.
- Warning shows the message but hands the decision to the user: Yes keeps the invalid value, No goes back to edit it.
- Information shows the message but does not block the entry; clicking OK keeps the value, and Cancel rejects it.
So Warning and Information report a problem without preventing it, and the value can end up in the cell either way. If the rule is meant to hold, the style has to be Stop. Reading Warning or Information as a guarantee that bad data cannot get in is the usual mistake here.
Why a rule covers less than you expect
A validation rule is narrower than a first look suggests, in two ways that matter for data quality.
First, it reacts to direct typing only. The reason is in Microsoft’s description of when validation runs: “Data validation is designed to show messages and prevent invalid entries only when users type data directly in a cell.” A value that arrives by paste, by dragging the fill handle, or as a formula result is never tested, so an out-of-range number can sit in a cell that carries a strict rule without ever tripping it.
Second, a rule does nothing to values that were already in place when you added it. Excel does not go back and re-check existing contents, so a column you validate today keeps whatever it held yesterday, valid or not, until something re-enters each cell.
How do you find data that already breaks a rule?
Because validation ignores pasted, calculated, and pre-existing values, Excel keeps a separate audit for them. On the Data tab, in the Data Tools group, select the arrow next to Data Validation and choose Circle Invalid Data. Excel draws a red circle around every cell whose current value fails its rule, wherever that value came from, so you can work through them. Fixing a cell clears its own circle, and Clear Validation Circles on the same menu removes them all at once. This is what turns validation from a guard on new typing into a check you can run across data that is already in the sheet.
How do you change or remove a rule?
Reopen Data > Data Validation on a cell that carries the rule. To adjust it, edit the Allow, Data, or message settings and select OK; checking Apply these changes to all other cells with the same settings pushes the edit to every cell sharing the rule in one pass. To take the rule off, go to the Settings tab and select Clear All, then OK. Removing a rule never touches the values already in the cells, it only stops restricting what gets entered next, the same reason a drop-down’s picked values survive after its list is cleared.
The short version
- Add: select the cells, Data > Data Validation, and on the Settings tab pick an Allow rule (Whole number, Decimal, List, Date, Time, Text length, or Custom) with a Data condition.
- Prompt vs enforce: the Input Message tab shows guidance when the cell is selected; the Error Alert tab enforces the rule.
- Only Stop blocks: Stop refuses the entry (Retry or Cancel); Warning and Information show a message but let the user keep the value.
- It is not airtight: validation fires on direct typing, so a pasted or filled value can bypass it, and it does not flag values already in the cell.
- Existing violations: Data > Data Tools > Data Validation > Circle Invalid Data rings the cells that break a rule; Clear Validation Circles removes the rings.
- Remove: Clear All on the Settings tab; picked values stay put. A drop-down is the List rule, built in creating a drop-down list.
A validation rule is a guard on new entries, which is why the problems it is meant to prevent still turn up elsewhere in a workbook. A number stored as text that was pasted in, a stale value that predates the rule, a formula result that fails a check without any warning, none of these are what validation was watching for. The rule keeps typed entries honest; auditing the values that got in another way is a separate job.
