📎 This article includes 1 downloadable practice file ↓
Most broken reports start with one bad entry: “Nort” instead of North, a 9-digit mobile number, a date typed as 31/02, a discount of 250%. Formulas and pivots then quietly give wrong answers. Data Validation stops the entry at the door, while it’s being typed.
In this article
Find it under Data › Data Validation. Select the cells first, then set the rule.
The built-in rule types
| Allow | Example | Settings |
|---|---|---|
| Whole number | Quantity 1–999 | between 1 and 999 |
| Decimal | Discount up to 25% | between 0 and 0.25 |
| List | Region | Source: =Lists!$A$2:$A$5 or typed North,South,East,West |
| Date | Invoice date in FY 2026-27 | between 01-04-2026 and 31-03-2027 |
| Text length | PIN code | equal to 6 |
| Custom | Anything a formula can test | Formula that returns TRUE for valid entries |
Custom rules: the real power
Select the range starting from its first cell (say B2), choose Custom, and write the formula for B2. Excel adjusts it for every other cell, just like conditional formatting.
PAN (5 letters, 4 digits, 1 letter):
=AND(LEN(B2)=10, SUMPRODUCT(--ISNUMBER(--MID(B2,{6,7,8,9},1)))=4,
SUMPRODUCT(--ISERROR(--MID(B2,{1,2,3,4,5,10},1)))=6)
Mobile (10 digits starting 6-9):
=AND(LEN(B2)=10, ISNUMBER(--B2), --LEFT(B2)>=6)
GSTIN contains the PAN in characters 3-12:
=AND(LEN(C2)=15, MID(C2,3,10)=B2)
No duplicates in the column:
=COUNTIF($B$2:$B$1000, B2) = 1
Email has one @ and a dot after it:
=AND(LEN(B2)-LEN(SUBSTITUTE(B2,"@",""))=1, ISNUMBER(FIND(".", B2, FIND("@", B2))))
Date not in the future:
=B2 <= TODAY()
Quantity only if Product is filled:
=AND(A2<>"", B2>0)These checks catch typing errors. They do not prove a PAN or GSTIN is genuine; for that you need the official portals.
Input message and error alert
The Input Message tab shows a hint when the cell is selected (“10-digit mobile, no spaces”). The Error Alert tab has three styles:
- Stop: blocks the entry. Use for anything that breaks formulas.
- Warning: asks “Continue?”. Use for unusual but possible values, like a very large quantity.
- Information: just a note; the entry goes in.
Write the error message in plain words: “PAN must be 5 letters, 4 digits, 1 letter, e.g. ABCDE1234F”, not “Invalid value”.
Find bad data already in the sheet
Validation checks only new typing. Data pasted in, or entered before the rule existed, can still be wrong. Use Data › Data Validation › Circle Invalid Data to draw red circles around every cell that breaks its rule.
Copy rules to other cells
Copy a validated cell, select the targets, then Paste Special › Validation. To find every validated cell in a sheet: Home › Find & Select › Data Validation.
Where people go wrong
| Problem | Cause |
|---|---|
| Rule works in the first cell only | Absolute reference ($B$2) in a custom formula that should move |
| Bad values still in the sheet | They were pasted; use Circle Invalid Data |
| Drop-down arrow missing | “In-cell dropdown” unticked |
| List shows blanks at the end | Source range includes empty rows; use a Table column |
Practice
Download the practice workbook below. The Entries sheet has a good and a bad value for each field; write the TRUE/FALSE rule formula for each, then add it as a real Custom validation. Each task has an automatic ✓ check, and the Answers sheet has a working formula if you get stuck.
Next: dependent drop-down lists.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Data validation practice workbookGood and bad sample values for PAN, mobile, quantity, FY dates, GSTIN, email, discount and region; 14 rule formulas to write, with checks.⬇ XLSX · 15 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.