How to Validate Data in Excel
"Validate data" means two different things in Excel: restricting what people can type, and checking the data you already have. Here's both — and how to find every row that breaks your rules.
Last updated: August 2026
| Valid? | ||
|---|---|---|
| a@b.com | → | OK |
| bad@ | → | not an email |
| (blank) | → | blank |
Method 1 — Data Validation (restrict new input)
- Select the range people will type into.
- Go to Data → Data Validation.
- Under Allow, choose Whole number, Date, List (for a dropdown), or Custom with a formula.
This is the built-in feature most people mean by "data validation" — but note it only controls future entries. It won't flag values that are already wrong.
Method 2 — validate existing data with formulas
- Is it a number?
=ISNUMBER(A2)— FALSE means it's text or blank. - Is it a duplicate?
=COUNTIF(A$2:A2, A2)>1. - Is it a real date?
=NOT(ISERROR(DATEVALUE(A2))). - Filter the helper column for the failures to see every offending row.
The 1-click way — Tellsheet
Validate Data checks an existing column against a rule you choose — number, real date, email, phone, non-blank, unique, or a regex pattern — and adds a column marking each row OK or the exact reason it failed ("not a number", "invalid date", "duplicate", "blank"). No stacked formulas, and it pairs with Column Health Scan, which auto-detects issues before you even define a rule. Fully local.
Frequently asked questions
What's the difference between Data Validation and validating existing data?
Data Validation (Data → Data Validation) restricts what can be typed going forward. It doesn't check data that's already there — for that, use formulas like ISNUMBER/COUNTIF or Tellsheet's Validate Data, which flags every offending row.
How do I check a column is all valid emails or numbers?
Add a helper column with a test and filter for failures, or use Tellsheet's Validate Data: choose the Email or Number rule and it marks each row OK or the reason it failed.
How do I flag duplicates or blanks?
Use COUNTIF for duplicates and ISBLANK for blanks, or Tellsheet's Validate Data with the Unique or Not-blank rule.
Can I validate against a custom pattern?
Yes — Validate Data's Regex rule tests each cell against a pattern you supply (e.g. an invoice code like ^[A-Z]{2}-\d{4}$) and flags the ones that don't match.
Related Excel guides
Validate a column in one click
Validate Data flags every row that breaks your rule — right inside Excel, with nothing leaving your computer.
Get Tellsheet free See pricing