It's 9:14 AM on a Monday. You just opened the Q2 Sales Tracker — shared across seven regional teams — and spot three entries in column D labeled "Pending" instead of "Won", "Lost", or "On Hold". Worse, two rows show "$12,500" as text, not a number. You know this will break the pivot table later today.
Quick Answer
Data validation in Excel means defining rules for what users can enter into cells — like restricting input to dates between 2024–2025, forcing selection from a list (e.g., "High", "Medium", "Low"), or blocking text longer than 50 characters. It lives under Data → Data Validation (Alt + A → V → V), and once set, it silently enforces integrity without macros or formulas.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| List (Dropdown) | Select cells → Data → Data Validation → Allow: List → Source: =$F$2:$F$5 | Standardized categories (Status, Region, Priority) | Source range must be on same sheet unless named; no search-as-you-type |
| Whole Number Range | Data → Data Validation → Allow: Whole number → Data: between → Min: 0, Max: 999 | Quantities, IDs, scores with hard bounds | Doesn’t prevent copy/paste overrides unless combined with worksheet protection |
| Date Range | Allow: Date → Data: between → Start: 2024-01-01, End: 2024-12-31 | Project deadlines, invoice dates, hire dates | Tolerates time components — a cell with 2024-06-15 14:30 passes unless you use custom formula |
| Custom Formula | Allow: Custom → Formula: =AND(LEN(A1)>0,ISNUMBER(FIND("@",A1))) | Complex logic (email format, uppercase-only, cross-cell dependencies) | Formula references adjust relatively — use $A$1 if you need absolute anchoring |
| Text Length | Allow: Text length → Data: less than or equal to → 100 | Notes fields, descriptions, comments | Counts spaces and line breaks — "Hello\nWorld" is 12 chars, not 10 |
| Decimal / Time / List with Blanks | Same dialog, different Allow options — e.g., Decimal for budgets, Time for shift logs | Financial inputs, scheduling, scientific measurements | Time validation accepts serial numbers — entering 0.25 still validates as 6:00 AM |
Method 1 Deep Dive
Let’s build a live project intake sheet where only valid statuses and budget ranges are accepted. Start with this sample data in A1:E6:
| Project | Owner | Status | Budget | Deadline |
|---|---|---|---|---|
| Cloud Migration | Sarah Chen | In Progress | $124,500 | 2024-09-30 |
| CRM Upgrade | Diego Morales | Planning | $89,200 | 2024-11-15 |
| HR Portal | Priya Patel | On Hold | $67,800 | 2024-08-22 |
| Security Audit | James Wilson | Won | $45,200 | 2024-07-10 |
| API Integration | Amina Diallo | Lost | $0 | 2024-06-30 |
Select C2:C100 (Status column). Press Alt + A → V → V. In the dialog:
- Allow: List
- Source: =$G$2:$G$6 (where G2:G6 holds "Won", "Lost", "In Progress", "On Hold", "Planning")
- Uncheck "Ignore blank" if empty cells should be allowed
- Under Input Message tab: Title "Project Status", Message "Select from approved statuses only"
- Under Error Alert tab: Style "Stop", Title "Invalid Entry", Message "Please choose a status from the dropdown."
The beauty of this approach is that it prevents typos like "WOn" or "onhold" at the point of entry — no post-hoc cleaning needed. And here’s the counterintuitive part: if you paste five rows into C2:C6, Excel applies validation to *all* pasted cells — even if your source data included invalid values. That’s because Excel evaluates validation *after* paste, not during.
Method 2 Deep Dive
Now protect the Budget column (D2:D100) so only amounts between $0 and $250,000 are accepted — and reject anything formatted as text.
Select D2:D100. Press Alt + A → V → V. Set:
- Allow: Decimal
- Data: between
- Minimum: 0
- Maximum: 250000
Then click the Settings tab again and uncheck "Ignore blank" — this ensures users can’t leave budget blank if required. Under Error Alert, choose Warning style (not Stop) so users can override *only if they acknowledge the warning*. Why? Because finance often needs to log placeholder $0 entries during early scoping.
Here’s what makes this elegant: Excel treats "123,456" entered as text as invalid — but "123456" (no comma) passes. So your team must enter raw numbers. To help them, add an Input Message: "Enter whole number (no $ or commas)".
Cheat Sheet
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Select target cells (e.g., B2:B50) | Range highlighted | Ctrl + Shift + ↓ (to extend down) |
| 2 | Open Data Validation dialog | Dialog appears with Settings tab active | Alt + A → V → V |
| 3 | Choose validation type & parameters | Rules applied to selected cells | Tab key to navigate fields |
| 4 | Add Input Message (optional) | Help text appears on cell selection | Click Input Message tab → enter title & text |
| 5 | Set Error Alert behavior | Controls what happens on invalid entry | Error Alert tab → choose Stop/Warning/Information |
| 6 | Apply and test with sample bad data | Try typing "XYZ" in a list cell — alert triggers | No shortcut — manual verification required |
| 7 | Clear validation from cells | Rules removed; cells accept any input | Alt + A → V → V → Settings tab → Clear All → OK |
