Data Validation — Build Error-Proof Input Systems with AI

Chapter 8: Data Validation & Smart Dropdowns

Data validation is one of Excel's most underused features — yet it can eliminate up to 80% of data entry errors before they happen. An Excel sheet without validation is like a warehouse without quality control: anyone can put anything anywhere. This chapter covers everything from basic rules to smart dropdown lists, with AI-assisted form design throughout.

Why Data Validation Is Excel's Most Overlooked Power Feature

Without validation, spreadsheets accumulate garbage: "approx 100" in a number column, "March" in a date column, 10-digit phone numbers where 11 are required, "Sales Dept" and "Sales Department" used interchangeably — making VLOOKUP and pivot tables unreliable.

Data validation addresses this at the source. Three things it does:

• Blocks invalid input — wrong format, out-of-range numbers, or duplicates are rejected before they enter the sheet.
• Standardizes data — dropdown menus replace free text, so department names are always consistent.
• Improves form UX — tooltips and error messages guide users on what to enter, reducing back-and-forth corrections.

ℹ️ Note

Access: Select cells → Data tab → Data Validation. One of the most valuable 5-minute investments you can make before sharing a spreadsheet.

Basic Validation Rules — 4 Cases

Case 1: Integer Between 1 and 100 (Grade Entry)

Block inputs like "98 pts", "excellent", or numbers outside 1–100

Allow: Whole number | Data: between | Min: 1 | Max: 100
    Input Message: "Enter an integer score from 1 to 100"
    Error Alert (Stop): "Score must be an integer between 1 and 100"

Case 2: Date Cannot Be Earlier Than Today (Deadline Field)

Prevent historical dates being entered as future deadlines

Formula in date field
    Allow: Date | Data: greater than or equal to | Start date: =TODAY()
    TODAY() recalculates each time the file opens — the validation threshold updates automatically.

Case 3: Phone Number Must Be 11 Characters

Enforce fixed-length phone number format

Allow: Text length | Data: equal to | Length: 11
    Tip: Format the column as Text first to prevent leading zeros from being dropped.

Case 4: Custom Formula — No Duplicates

Each product code must be unique in the column

Custom formula (applied to A2:A100)
    =COUNTIF($A$2:$A$100,A2)
    Logic: if COUNTIF returns 1 (first occurrence) validation passes; if it returns 2+ a duplicate exists and input is blocked.
    Key: $A$2:$A$100 is absolute (whole range); A2 is relative (current cell).

I need an Excel data validation custom formula for column B (B2:B200). Requirements:
- Must be numeric only (no letters)
- Must be exactly 11 characters
- Must start with "1" (mobile number format)
- No duplicates allowed in the column

Current cell is B2. Please give me the formula and explain each part.

Dropdown Lists: Static vs Dynamic

Static List — Type options directly

Allow: List | Source: North,South,East,West
  (Comma-separated, no spaces — use your regional list separator if needed)
  Drawback: Must edit validation settings every time options change.

Dynamic List — Reference a cell range

Allow: List | Source: =$F$2:$F$10
  Update options by editing column F — validation adjusts automatically.

Super Table + INDIRECT — Auto-expanding dropdown

1. Select options → Ctrl+T → name the table "ProductDB"
  2. Data Validation → Source: =INDIRECT("ProductDB[Product Name]")
  New rows added to the table immediately appear in the dropdown — zero maintenance.

Case: Dependent Dropdown (Province → City)

Selecting a province filters the city dropdown automatically

1
Create a named range for each province's cities. Name must match the province option exactly (e.g., name the California cities range "California").


2
Province column (A): standard list validation with province names.


3
City column (B): Allow: List | Source: =INDIRECT(A2)

INDIRECT(A2) reads the text in A2 and treats it as a range name.
    When A2 = "California", INDIRECT returns the California named range → city list updates.

Case: Multi-Select Dropdown (VBA)

In my Excel sheet, column D (D2:D100) has a data validation dropdown. I want users to be able to select multiple items from the dropdown, with each selection appended using a comma separator. If an item is selected again, it should be removed (toggle behavior). Please write the VBA Worksheet_Change event code and tell me exactly where to paste it.

Input Messages & Error Alerts

✅ Tip

Best practice: Use Stop for mandatory format constraints, Warning for business-rule soft limits, and Information for helpful guidance. Never use Stop for every field — it frustrates users and leads to workarounds.

Circle Invalid Data — Audit Existing Records

When you inherit a spreadsheet with existing data and add validation rules afterward, existing bad data isn't automatically flagged. Use Circle Invalid Data to highlight all rule violations at once.

1 Set validation rules on the column (existing data is not yet checked)

2 Data tab → Data Validation dropdown arrow → Circle Invalid Data

3 Red ovals appear on every cell that fails the validation rule — review and correct each one

4 After corrections: Data → Data Validation dropdown → Clear Validation Circles

Comprehensive Case: Employee Onboarding Form with AI

I need to build an Excel employee onboarding form for HR to fill in when new staff join. The company has ~200 people across Engineering, Product, Design, Sales, Operations, and Admin.

Please design the form including:
1. All fields needed (at least 15), grouped by category
2. Data type and validation rule for each field
3. Which fields need dropdown menus (list the options)
4. Custom formula validation for any complex rules (provide the formulas)
5. Layout recommendation — how to organize sections

Goal: a non-technical HR coordinator should be able to fill it out quickly and correctly, minimizing downstream data cleanup.

Data Validation Cheat Sheet