📎 This article includes 1 downloadable practice file ↓
In this article
Lesson 2 said “same spelling every time”. People don’t manage that: North, north, Nort and N all appear in the same column, and every report that counts “North” comes out wrong. Data validation stops it at the source: the cell only accepts what you allow.
A drop-down list in 30 seconds
- Type the allowed values on a sheet called Lists (North, South, East, West), one per cell.
- Select the cells where people will enter regions.
- Data › Data Validation › Allow: List › Source: select the list cells (
=Lists!$A$2:$A$5). - OK. Each cell now has a drop-down arrow, and typing anything else is refused.
For a short fixed list you can type the source directly: Yes,No or Paid,Pending,Cancelled.
Number and date limits
| Allow | Example rule |
|---|---|
| Whole number | Quantity between 1 and 999 (no decimals, no zero) |
| Decimal | Discount between 0 and 0.25 (up to 25%) |
| Date | Between 01-04-2026 and 31-03-2027 (this financial year) |
| Text length | Equal to 6 for a PIN code, 10 for a mobile |
Custom rules with a formula
Choose Custom and write a formula that’s TRUE for valid entries. Write it for the first selected cell (say B2):
=AND(LEN(B2)=10, ISNUMBER(--B2), --LEFT(B2)>=6) ' 10-digit mobile starting 6-9
=COUNTIF($B$2:$B$500, B2) = 1 ' no duplicates in the column
=B2 <= TODAY() ' no future datesA full PAN and GSTIN example is in data validation basics. These rules catch typing errors; they don’t confirm a PAN or GSTIN actually exists.
Help people get it right
- Input Message tab: a note that appears when the cell is selected (“Choose a region from the list”).
- Error Alert tab: Stop blocks the entry; Warning asks “Continue?”; Information just informs. Write a useful message: “Quantity must be a whole number from 1 to 999”.
Check data that’s already there
Validation only checks new typing. For existing data, Data › Data Validation › Circle Invalid Data draws red circles round every cell that breaks its rule. Fix them, then Clear Validation Circles.
Dependent drop-downs
A second list that changes with the first (Category → Item, State → City) is the next step: see dependent drop-down lists.
Where beginners go wrong
| Problem | Cause |
|---|---|
| No arrow on the cell | “In-cell dropdown” unticked |
| Blank options at the end of the list | Source range includes empty cells |
| Rule only works in the first cell | $ on the cell in a custom formula ($B$2 instead of B2) |
| Bad data still present | It was there before the rule; use Circle Invalid Data |
Practice
Download this lesson’s workbook below. The Entries sheet has a good and a bad value for each field; write each rule as a TRUE/FALSE formula, then add it as a real validation rule. Type your answers in the yellow column; the Check column turns green when you’re right, and the Answers sheet shows a working formula for every task.
📎 Practice files for this article
- 📗Lesson 15 practice workbookGood and bad sample values for PAN, mobile, quantity, dates, GSTIN, email, discount and region: write the rules, then apply them.⬇ 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.