Excel Data Validation: Rules, Messages, and Audits

Learn to create Excel data validation rules, input messages, error alerts, custom formulas, drop-downs, and audits for invalid entries.

By: Ethan Mercer Published: October 5, 2026

Excel data validation controls what users can type into selected cells. You can limit entries to whole numbers, decimals, dates, times, text of a certain length, items from a list, or values that satisfy a custom formula.

A useful rule has three parts: a precise condition, guidance before entry, and a clear response when the entry is invalid. It also needs testing. Validation catches many typing mistakes, but it is not a substitute for auditing imported, pasted, calculated, or macro-generated data.

The menu paths below use current desktop Excel for Windows. Excel for Mac and Excel for the web support core data-validation features, although labels, dialog layouts, and some auditing tools can differ.

Know what data validation can—and cannot—do

Data validation can	Data validation cannot reliably do alone
Restrict values typed directly into a cell	Guarantee that copied, filled, calculated, or macro-entered values are valid
Offer a list of approved choices	Repair inconsistent data that already exists
Display instructions when a cell is selected	Explain the business reason for a rule unless you write a useful message
Reject, warn about, or report an invalid typed entry	Protect confidential data or prevent every kind of workbook change
Use formulas and other cells to define a condition	Replace a final review of important data

Think of validation as a controlled entry point. It improves consistency at the moment of manual input; a separate audit catches values that arrived through other routes.

Choose the validation type that matches the rule

Allow option	Use it for	Example rule
Whole number	Counts and quantities with no decimal part	Allow order quantities from 1 through 500.
Decimal	Measurements, rates, or percentages	Allow a discount from 0% through 20%.
List	A controlled set of text or numeric choices	Allow only Draft, Approved, or Rejected.
Date	Dates inside or outside a defined period	Allow due dates during the current quarter.
Time	Times within a scheduling window	Allow appointments from 08:00 through 17:00.
Text length	A minimum, maximum, or exact character count	Limit a reference code to 12 characters.
Custom	A formula that evaluates a condition	Require an end date to be on or after its start date.

Microsoft’s data-validation reference documents each built-in rule type and formula-based examples.

Create a validation rule from start to finish

Suppose cells D2:D200 should contain order quantities from 1 through 500.

Select D2:D200.
Open Data > Data Tools > Data Validation.

On the Settings tab, set Allow to Whole number.
Set Data to between.
Enter 1 as the minimum and 500 as the maximum.
Decide whether blank cells should be allowed. Keep Ignore blank selected only when an empty entry is acceptable.
Add an input message and error alert, then select OK.
Test the lower limit, upper limit, a valid middle value, an invalid value, text, and a blank.

Boundary testing matters. A rule that should include 1 and 500 is different from a rule that accepts only values greater than 1 and less than 500.

Write an input message that helps before the mistake

On the Input Message tab, select Show input message when cell is selected. Use a short title and state the exact requirement.

Weak message	Useful message
Enter a valid value.	Enter a whole-number quantity from 1 through 500.
Wrong date.	Enter a date from 1 Oct 2026 through 31 Dec 2026.
Choose status.	Select Draft, Approved, or Rejected from the list.

The message appears when the cell is selected. It should describe the accepted value, not merely announce that a rule exists.

Choose the correct error-alert style

The Error Alert tab controls what happens after a user types an invalid value. The three styles do not enforce the rule in the same way.

Style	Behavior	Use it when
Stop	Rejects the invalid typed entry unless the user retries with a valid value or cancels	The rule is mandatory.
Warning	Warns about the invalid entry but lets the user accept it	Exceptions are allowed after deliberate confirmation.
Information	Reports that the value is invalid but still lets the user keep it	The rule is advisory.

Use Stop when invalid typed data must be rejected. Warning and Information messages can still leave an invalid value in the cell.

Microsoft’s advanced data-validation guide explains the choices users receive with each alert style.

Create a basic drop-down list

A List rule gives users a defined set of choices and reduces spelling variations. Keep the source values in worksheet cells when the list will change; a source range is easier to maintain than a long string typed into the dialog.

Enter the approved items in a single row or column without including a heading in the selectable range.
Select the cells that need the list.
Open Data Validation and choose List under Allow.

In Source, select the cells containing the approved items.
Keep In-cell dropdown selected.
Choose whether blanks are allowed, configure the messages, and test the list.

For a list that grows often, store the source items in an Excel table so list-based drop-downs can follow additions and removals. Microsoft’s drop-down list guide covers source ranges, tables, and list maintenance in more detail.

Validate one cell against another with a formula

Choose Custom when the valid result depends on a relationship rather than a fixed limit. Suppose column B contains a start date and column C contains an end date. To require a numeric end date that is on or after the start date:

Select C2:C100, with C2 as the active cell.
Open Data Validation and set Allow to Custom.
Enter =AND(ISNUMBER(B2),ISNUMBER(C2),C2>=B2) in the Formula box.
Clear Ignore blank if every row must contain an end date.

Add a Stop alert such as “Enter an end date on or after the start date.”
Select OK and test several rows.

The row references are relative, so Excel evaluates B3 and C3 for row 3, then continues down the selected range. Use dollar signs only for cells or ranges that should stay fixed when the rule is applied elsewhere.

Copy a validation rule without rebuilding it

Copy a cell that already has the correct rule, select the destination, and use Paste Special > Validation. This transfers the validation settings without replacing the destination’s values or formatting. Check relative references afterward because copied formulas may adjust to the destination.

Microsoft’s Paste Special reference lists Validation as a separate paste option.

Audit values that bypassed the rule

Excel does not automatically flag old invalid values merely because you add validation to cells that already contain data. Validation alerts can also be bypassed when values are copied, filled, returned by a formula, or entered by a macro.

Select the cells you want to audit.
Open the menu beside Data > Data Validation.
Choose Circle Invalid Data.

Review and correct the cells marked with red circles.
Choose Clear Validation Circles when the review is complete.

The circles do not change the data; they identify cells that fail their current rules. Microsoft’s invalid-data audit guide confirms that the check includes values entered by typing, copying, filling, formulas, or macros.

Find, change, or remove validation rules

Find validated cells

Use Home > Find & Select > Go To Special > Data Validation. Choose All to select every validated cell in the search area, or Same to find cells with rules matching the active cell. This is useful when validation exists but is not visually obvious.

Change a rule consistently

Select a cell with the rule, open Data Validation, and revise the settings. When appropriate, use Apply these changes to all other cells with the same settings so matching rules do not drift apart.

Remove validation without deleting the value

Select the cells, open Data Validation, choose Clear All on the Settings tab, and select OK. The cell contents remain; only the validation rule and its messages are removed.

Fix common data-validation problems

Problem	What to check
Invalid typed values are still accepted	Confirm that the error alert is enabled and its style is Stop rather than Warning or Information.
Pasted data ignores the rule	This is a documented limitation. Audit the range with Circle Invalid Data after pasting or importing.
The drop-down arrow is missing	Confirm that the rule uses List and that In-cell dropdown is selected.
Data Validation is unavailable	Finish editing the active cell. If the command remains unavailable, check whether the worksheet is protected or the workbook is in a sharing mode that restricts changes.
A custom rule behaves differently by row	Review relative and absolute references, starting with the active cell used when the rule was created.
Existing bad values show no alert	Adding a rule does not retroactively display alerts. Use Circle Invalid Data to audit existing entries.
A formula-based rule stops working	Check referenced cells, formula errors, and calculation settings. Validation can be affected when its formula cannot evaluate correctly.

Excel data-validation questions

Does data validation remove incorrect data?

No. It controls or warns about new manual entries, depending on the error-alert style. Use Circle Invalid Data to identify existing values that fail the rule, then correct them deliberately.

What is the difference between data validation and conditional formatting?

Data validation controls or evaluates input. Conditional formatting changes a cell’s appearance when a condition is met. They can work together: validation defines what is acceptable, while conditional formatting can make review states or exceptions more visible.

Can I copy validation without copying the cell’s value?

Yes. Copy the validated cell and choose Paste Special > Validation at the destination.

How do I allow only nonblank entries?

Clear Ignore blank and use a rule that rejects empty input. For a Custom rule, include an explicit nonblank test such as A2<>"", adjusted for the first cell in the validated range.

Pair entry rules with an audit

Choose the validation type that matches the business rule, write messages that tell users exactly what belongs in the cell, and use Stop only when exceptions are not allowed. Then test the boundaries and audit the range after pasting or importing data. That combination turns validation from a dialog-box setting into a dependable data-entry process.