Data Validation: Prevent Errors Before They Happen

Data validation restricts what a user can type into a cell — a dropdown list, a number range, a date range, or a custom formula rule — and rejects or flags anything that doesn't match.

What are the basic data validation types?

Excel's built-in rule types cover the most common cases: a picklist, a number or date range, or a text length limit.

1. Dropdown List (Most Common)

Select cells → Data → Data Validation → List

Source: Red, Green, Blue
Or reference a range: =Sheet2!$A$2:$A$10

Creates dropdown with predefined choices
Users can only select from the list

2. Number Ranges

Validation → Whole Number → Between → 1 and 100

Use cases:
- Age fields (1-120)
- Percentage inputs (0-100)
- Quantity limits (1-999)

3. Date Restrictions

Validation → Date → Greater than → =TODAY()

Prevents past dates (for deadline fields)
Or: Between → 1/1/2024 and 12/31/2024

4. Text Length Limits

Validation → Text Length → Less than or equal to → 50

Perfect for:
- Database imports with character limits
- Social media post character counts
- Form fields with size restrictions

How do I write a custom validation formula?

Choose Custom as the validation type and enter any formula that returns TRUE or FALSE for the active cell — TRUE lets the entry through, FALSE triggers the error alert.

Email Validation

Select cells → Validation → Custom → Formula:

=AND(
    ISNUMBER(FIND("@", A2)),
    ISNUMBER(FIND(".", A2)),
    LEN(A2) > 5
)

Ensures @ and . are present and length > 5

Prevent Duplicates

=COUNTIF($A$2:$A$100, A2) = 1

Applied to A2:A100
Rejects any value that already exists in the range

Dependent Dropdowns

Setup:

Sheet2:
A1: Electronics  B1: Clothing    C1: Food
A2: Laptop       B2: Shirt       C2: Apples
A3: Phone        B3: Pants       C3: Bread

Step 1: Cell A2 validation = List → Electronics, Clothing, Food
Step 2: Cell B2 validation = List → =INDIRECT(A2)

When user selects "Electronics" in A2,
B2 dropdown shows items from Electronics column!

Conditional Validation Based on Another Cell

=IF($B2="Active", LEN(A2)>0, TRUE)

If column B = "Active", column A is required (not blank)
If column B ≠ "Active", any value allowed

How do I add a helper message or custom error to validation?

Data Validation has two extra tabs beyond the rule itself: Input Message shows guidance when the cell is selected, and Error Alert controls what happens — and what's shown — when an entry fails the rule.

Input Message (Helpful Guidance)

Data Validation → Input Message tab:
Title: "Enter your employee ID"
Message: "Use format: EMP-####"

Shows when user selects the cell

Custom Error Messages

Data Validation → Error Alert tab:
Style: Stop (🚫) / Warning (⚠️) / Information (ℹ️)
Title: "Invalid Entry"
Message: "Please enter a valid email address"

Stop = Rejects invalid entry
Warning = Warns but allows entry
Information = Just notifies

Can a dropdown list update itself automatically?

Yes — point the List source at a spilled dynamic array (using the # spill reference) built with UNIQUE and FILTER, and the dropdown's options update whenever the source data changes.

Auto-Updating Unique List

Helper cell (Z1):
=UNIQUE(FILTER(Data!A:A, Data!A:A<>""))

Validation → List → Source: =Z1#

The # references the entire spilled array
Dropdown auto-updates when data changes!

Filtered List Based on Criteria

Show only "Active" customers in dropdown:
=UNIQUE(FILTER(Customers!A:A, Customers!B:B="Active"))

What custom validation formulas are useful in practice?

A handful of one-line Custom formulas cover most real-world formatting rules — phone number length, weekday-only dates, and case enforcement among them.

Use Case	Validation Rule
Phone number format	=LEN(A2)=10
No weekends	=WEEKDAY(A2,2)<6
Future dates only	=A2>TODAY()
Uppercase only	=EXACT(A2,UPPER(A2))

How do I find, copy, or remove existing validation rules?

Validation rules aren't visible on the sheet, so use Find & Select to locate them and the Data Validation dialog to copy, clear, or audit them.

• Find cells with validation: Home → Find & Select → Data Validation
• Copy validation: Use Format Painter or copy/paste
• Remove validation: Data → Data Validation → Clear All
• Circle invalid data: Data → Data Validation → Circle Invalid Data