Yes, you can apply data validation in Excel. But if you skip the input message and rely only on the dropdown arrow, you’ve already lost control of your data.
The Setup
You’re managing a vendor onboarding sheet for Alibaba’s internal procurement team. Eight new suppliers submitted forms last week—some typed dates as text, others entered "$1200" instead of 1200, and two wrote "N/A" in the Contract Value column. Your job: clean this before it hits the ERP system.
| Vendor Name | Contact Email | Contract Start | Contract Value | Status |
|---|---|---|---|---|
| Skyline Logistics | jlee@skyline-logistics.cn | 2024-06-01 | 24500 | Active |
| NeoFab Manufacturing | contact@neofab-tech.in | Jun 15, 2024 | $32,750 | Pending |
| TerraGreen Solutions | support@terragreen.co.ke | 2024/07/22 | 18900 | Active |
| Orion MedEquip | info@orion-med.ae | 2024-08-01 | N/A | Draft |
| VistaCore Systems | admin@vistacore.jp | 2024-05-30 | 41200 | Active |
| Aurora Textiles | hello@auroratextiles.bd | May 2024 | 27500 | Pending |
| Helix Biotech | data@helixbio.sg | 2024-09-10 | 15600 | Draft |
| Nexus Logistics | ops@nexuslogi.pk | 2024-07-05 | $19,800 | Active |
The Challenge
You need to enforce structure—but not break usability. Users must enter:
- Dates in YYYY-MM-DD format (no text like "June 15")
- Contract values as numbers only (no $, commas, or N/A)
- Status limited to exactly three options: Active, Pending, Draft
Here’s what makes it tricky: Excel won’t stop pasted values unless you set Error Alert to Stop. And if you forget to check "Ignore blank", empty cells bypass validation entirely. Also—most people don’t know that Ctrl + ; inserts today’s date, but that date won’t pass validation unless the cell is formatted as Date *and* the validation rule includes date range logic.
Walking Through It
Start with the Status column (E2:E9). Select that range. Press Alt + A + V + V—that’s the keyboard shortcut for Data Validation.
Step 1: List validation
Under Settings tab → Allow: List. Source: Active,Pending,Draft (no spaces, no quotes, comma-separated). Check "Ignore blank". Click OK.
| Status (Before) | Status (After) |
|---|---|
| Active | Active |
| Pending | Pending |
| Draft | Draft |
| N/A | ❌ blocked — alert appears |
Step 2: Number validation
Select D2:D9. Alt + A + V + V again. Allow: Whole number. Data: greater than or equal to. Minimum: 0. Uncheck "Ignore blank"—you want blanks rejected too. Under Error Alert tab, change Style to Stop (not Warning). Title: "Invalid Contract Value". Message: "Enter a whole number ≥ 0. Remove $, commas, and text."
Counterintuitive tip: If you paste $24,500 into a validated cell, Excel strips the $ and comma *before* checking the rule—so it passes. To prevent that, use a custom formula instead: =AND(ISNUMBER(D2),D2>=0,LEN(TRIM(D2))=LEN(SUBSTITUTE(SUBSTITUTE(D2,"$",""),",",""))). But that’s overkill for most teams. Stick with Whole Number + Stop alert + clear instructions.
Step 3: Date validation
Select C2:C9. Alt + A + V + V. Allow: Date. Data: between. Start date: 2024-01-01. End date: 2025-12-31. Input Message tab: Title: "Contract Start Date". Message: "Use YYYY-MM-DD (e.g., 2024-07-15)". Error Alert Style: Stop.
The Result
After applying all three rules, here’s how the sheet behaves:
| Vendor Name | Contract Start | Contract Value | Status |
|---|---|---|---|
| Skyline Logistics | 2024-06-01 | 24500 | Active |
| NeoFab Manufacturing | ❌ edit blocked | ❌ edit blocked | Pending |
| TerraGreen Solutions | 2024-07-22 | 18900 | Active |
| Orion MedEquip | 2024-08-01 | 15000 | Draft |
| VistaCore Systems | 2024-05-30 | 41200 | Active |
| Aurora Textiles | ❌ edit blocked | 27500 | Pending |
| Helix Biotech | 2024-09-10 | 15600 | Draft |
| Nexus Logistics | 2024-07-05 | 19800 | Active |
What Could Go Wrong
Here are the three mistakes I see most often in live workshops—each with real consequences:
| Symptom | Cause | Fix |
|---|---|---|
| Dropdown appears, but users type anything anyway | "In-cell dropdown" checkbox is unchecked in Data Validation dialog | Reopen validation → Settings tab → check "In-cell dropdown" |
| Paste still works even with validation applied | Error Alert Style is set to "Warning" or "Information" instead of "Stop" | Go to Error Alert tab → Style → select "Stop" → OK |
| Validation disappears when copying cells | User copied with Ctrl+C then pasted with Ctrl+V (overwrites validation) | Use Paste Special → Values Only (Alt+E+S+V) or paste into blank columns first |
Next step: Audit your current sheets. Run =CELL("protect",A1) in an empty column next to each validated range—if it returns "0", protection is off. Then test paste behavior manually. If pasting bypasses validation, go straight to the Error Alert tab and switch to Stop. Don’t wait for the audit report.
