Data validation controls what users can enter in cells. Create dropdowns, restrict inputs, and show helpful messages to prevent bad data.

Basic Validation Types

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

Advanced: Custom Formulas

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

Input Messages & Error Alerts

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

Dynamic Dropdown from Formula

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"))

Practical Examples

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))

Managing & Troubleshooting

• 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