The short answer: select the cells, go to Data > Data Validation, pick a type under Allow, set the criteria, and choose an Error Alert style. Excel offers seven types: whole number, decimal, list, date, time, text length and custom. The catch nobody mentions is that validation only fires on typing. Pasting, filling, formula results and macros all slip past it, which is why a validated sheet can still be full of bad values.
Data validation is the most under-used feature in Excel, and it is the one that would prevent the most damage. A model breaks because somebody typed 1,250 with a comma in a cell a formula multiplies. A schedule breaks because a date arrived as text. A category column that should hold six values ends up holding nineteen, four of which are misspellings of the same word. Validation catches all of that at the point of entry, which is the only cheap place to catch it.
It is also the feature people half-learn. Almost everyone who has used it has used it once, to build a dropdown. That is one of seven rule types, and the other six are where the real protection lives.
What is data validation in Excel?
Data validation is a rule attached to a cell that restricts what can be typed into it. You define what counts as acceptable, whole numbers between 1 and 100, a date after today, text no longer than 10 characters, and Excel refuses or flags anything else. Two optional extras come with it: an input message that appears when the cell is selected, and an error alert that appears when the rule is broken.
The important mental model: validation is a gate on typed input, not a property of the data. It does not audit what is already in the cell, and it does not stop other routes into the cell. Set a rule on a column that already contains bad values and nothing happens; the bad values stay exactly where they are.
How do I add data validation in Excel?
Select the cells first, then go to Data > Data Validation. On the Settings tab, choose a type from the Allow dropdown, then fill in the criteria fields that appear, since they change depending on what you picked. Use the Input Message tab to write a short prompt shown when someone selects the cell, and the Error Alert tab to decide what happens when the rule is broken. Click OK.
Two habits make this worth doing. Select the whole column range you expect to fill, not just the first cell, because validation applies only where you put it. And write the input message, even a five word one. A rule that rejects an entry without telling anyone what was wanted is a rule people work around.
What are the data validation rule types in Excel?
The Allow dropdown offers seven types. Most guides cover the list type and stop, which is a shame, because the numeric and custom rules are the ones that protect calculations.
| Allow type | What it restricts | Good for |
|---|---|---|
| Whole number | Integers only, within a range you set | Quantities, headcount, unit counts, anything that cannot be fractional |
| Decimal | Any number, within a range | Amounts, rates, percentages, prices |
| List | Values from a range or a typed list | Categories, statuses, account codes |
| Date | Real dates, within a range | Invoice dates, start and end dates, period cutoffs |
| Time | Real times, within a range | Shift starts, appointment slots, timesheets |
| Text length | Character count, not content | Reference codes, ZIP codes, fixed-width IDs |
| Custom | Anything a formula can express, must return TRUE or FALSE | Cross-cell rules, pattern checks, no-duplicates rules |
The date and time types carry a bonus most people miss. They accept only genuine Excel date or time serial numbers, so they reject text that merely looks like a date. If your sheet has a chronic problem with dates arriving as text, a date validation rule on the column turns a silent problem into a visible one.
Building a dropdown is the list type specifically, and it has enough depth of its own (dependent lists, sources that expand automatically, lists on another sheet) that it is covered separately in how to create a drop down list in Excel. Everything below applies to the other six.
How do I restrict a cell to a number range in Excel?
Select the cells, open Data > Data Validation, set Allow to Whole number or Decimal, then choose a comparison in the Data box: between, not between, equal to, greater than, less than, and so on. Fill in the Minimum and Maximum. Whole number rejects 4.5; Decimal accepts it. Pick based on whether a fraction is meaningful in that column.
You can point Minimum and Maximum at cells rather than typing constants. Putting the bounds in labelled cells at the top of the sheet, then referencing them, means the limits are visible and editable without reopening the dialog. On a shared workbook that alone prevents a lot of confusion.
How do I use a custom formula in data validation?
Set Allow to Custom and enter a formula that returns TRUE for acceptable entries and FALSE for everything else. Write it as though it applies to the top-left cell of your selection; Excel adjusts the references for the rest, the same way it does when you copy a formula.
Four that earn their keep:
=ISNUMBER(A2)rejects anything that is not a real number, including numbers stored as text. This is the single most useful validation rule in a finance workbook.=COUNTIF($A$2:$A$500,A2)=1blocks duplicates in a column, useful for invoice numbers and employee IDs.=A2>=B2in an end-date column stops anyone entering an end date before the start date in column B.=LEFT(A2,3)="INV"enforces a prefix on reference codes.
Two traps. The formula must evaluate to a single TRUE or FALSE, so a formula returning a number or text will not behave. And absolute versus relative references matter enormously here: in the COUNTIF example the range is locked with dollar signs while the criterion is not, which is what makes the rule check each row against the whole column. If that distinction is fuzzy, absolute references in Excel covers it.
What is the difference between Stop, Warning and Information alerts?
The Error Alert tab controls what happens when someone breaks the rule, and the style you choose decides whether the rule is a wall or a suggestion.
| Style | What the user sees | Can they proceed? |
|---|---|---|
| Stop | An alert with Retry and Cancel | No. The entry is rejected outright |
| Warning | An alert asking Yes, No or Cancel | Yes, by choosing Yes |
| Information | An alert with OK and Cancel | Yes, by clicking OK |
Stop is the default and the right pick for anything that feeds a calculation. Warning suits columns where an unusual value is possible but should be deliberate, an expense over $10,000, say. Information is closer to a note than a control.
One detail worth knowing: if you clear the "Show error alert after invalid data is entered" checkbox, the rule still exists and still marks entries as invalid internally, it just stops interrupting anyone. Combined with Circle Invalid Data below, that turns validation into a silent audit rather than a gate, which is often exactly what you want on data somebody else produced.
Why is my data validation not working?
Almost always because the data did not arrive by typing. Excel checks validation rules on manual entry only. If a value is pasted in, filled down, produced by a formula, or written by a macro, the rule does not fire and no alert appears. Paste is the big one: pasting over a validated cell can also overwrite the rule itself, so the protection disappears along with the check.
This is not a bug, it is documented behavior, and it is the reason validated sheets still end up wrong. A few practical responses:
| Symptom | Cause | Fix |
|---|---|---|
| Invalid values appeared with no alert | Data was pasted, filled, or written by a macro | Run Circle Invalid Data to find them; paste with Values Only as a habit |
| The rule vanished from some cells | A normal paste overwrote the validation along with the value | Reapply the rule, then protect the sheet to block edits |
| A formula result breaks the rule | Validation never checks calculated results | Flag it with conditional formatting instead |
| The dialog is greyed out | The sheet is protected, or the workbook is shared | Unprotect the sheet, edit the rule, protect it again |
| Nothing is rejected at all | The rule was applied to one cell, not the range | Select the full range and reapply |
If a workbook genuinely has to resist bad input, validation alone will not do it. Pair it with sheet protection so people can only edit the cells you intend, and use conditional formatting for anything computed.
How do I find cells that break the rule?
Use Circle Invalid Data. Open the small arrow beside Data > Data Validation and choose Circle Invalid Data. Excel draws a red oval around every cell whose current contents break its rule, including values that were pasted in and never checked. Choose Clear Validation Circles to remove them, and note the circles do not print or save.
This is the feature that makes validation useful on data you did not type. Apply rules to a column after the fact, circle the invalid entries, and you have an instant map of everything that needs attention. It is the fastest audit in Excel and hardly anyone knows it exists.
How do I copy data validation to other cells?
Copy a cell that already has the rule, select the target range, then use Paste Special and choose Validation. That copies the rule and nothing else, leaving the existing values and formatting alone. A plain paste would bring the source value across too, which is rarely what you want.
To find every cell in a sheet that already carries a rule, press F5, click Special, and choose Data Validation. Selecting All highlights every validated cell; selecting Same highlights only those matching the active cell's rule. That is how you audit an inherited workbook without opening the dialog fifty times.
How do I remove data validation in Excel?
Select the cells, open Data > Data Validation, and click Clear All, then OK. To strip validation from an entire sheet in one move, select a single validated cell, open the dialog, tick "Apply these changes to all other cells with the same settings", then Clear All.
Before you clear anything, check why the rule is there. On a workbook somebody else built, a validation rule is often the only surviving documentation of what a column is allowed to contain.
Using data validation on data you converted from a PDF
This is where validation stops being a data-entry feature and becomes a quality check. Data that arrives from a PDF to Excel conversion fails in a small number of predictable ways, and each one maps to a rule.
Apply =ISNUMBER(A2) to the amount column and circle the invalid data. Every cell that lights up is a number that arrived as text, usually because a currency symbol or a thousands separator came across with it, which is exactly why SUM returns zero on a column that looks perfectly numeric. Apply a Date rule to the date column and circle again: the entries that light up are text pretending to be dates and will not sort correctly. Apply a Decimal rule with sensible bounds to catch a decimal point read one place off by OCR, which is the error most likely to survive a visual review because $1,250.00 and $12,500.00 look similar in a long column.
Order matters. Convert, then validate, then circle, then fix, then clear the circles. Doing it before the conversion is finished means re-running it. The broader routine is in how to check a PDF to Excel conversion for errors, and the cleanup itself in cleaning up data after a conversion. If the source is a monthly statement, converting bank statement PDFs to Excel is the same workflow on a schedule.
The reason this works is that validation rules are cheap to write and reusable. Build the three rules once on a template sheet, copy them forward with Paste Special every month, and the check costs about thirty seconds. It is the same instinct that, once the data has outgrown a workbook and lives in a warehouse, turns into automated monitoring of freshness, volume and schema. In a spreadsheet you get the entry-level version for free.
The short version
- Data > Data Validation, choose from seven Allow types, set criteria, set an error alert.
- The list type builds dropdowns; the other six protect calculations and matter more.
- Custom rules take a formula returning TRUE or FALSE.
=ISNUMBER(A2)is the most useful one in a finance sheet. - Stop rejects, Warning lets people override, Information just notifies.
- Validation only fires on typed entry. Pasting, filling, formulas and macros all bypass it, and pasting can wipe the rule.
- Circle Invalid Data audits values that were never checked, which makes validation work on converted and imported data.
- Copy rules with Paste Special then Validation; find them with F5, Special, Data Validation.