Google Sheets Data Validation Not Working? Here’s How To Fix It

Your data validation in Google Sheets isn’t working, dropdowns don’t appear, invalid values are allowed, or rules don’t apply correctly.
This usually shows up as missing dropdowns, incorrect restrictions, or users entering anything without warnings.

This guide fixes it step by step.

Why the Issue Happens

• Wrong range applied for validation
• Criteria referencing incorrect or empty range
• Dropdown source range not updating
• Text vs number mismatch
• Extra spaces in source values
• “Show warning” enabled instead of “Reject input”
• Merged cells interfering with validation
• Copy-paste overriding validation rules

Step-by-Step Fixes

Step 1: Check the Applied Range

Validation only works where it’s applied.

Fix:

• Go to Data → Data validation
• Check “Apply to range”

Example:

B2:B100

If your data goes beyond this, validation won’t apply.

Step 2: Verify Validation Criteria

Check if criteria is correctly set.

Example (dropdown list):

List from a range → Sheet2!A2:A10

If this range is empty or incorrect, dropdown won’t show.

Fix:

• Ensure source range contains valid values
• Re-select range manually

Step 3: Fix Dropdown Not Showing

If dropdown arrow is missing:

Check:

• Is “Show dropdown list in cell” enabled?
• Is the cell formatted correctly?

Fix:

• Reapply validation rule
• Ensure no conflicting formatting

Step 4: Enforce Strict Validation

If invalid data is allowed, settings are wrong.

Fix:

• Change from:
  • “Show warning”
• To:
  • “Reject input”

This ensures only valid entries are accepted.

Step 5: Fix Text vs Number Mismatch

Example issue:

• Validation list = 100, 200 (numbers)
• User enters "100" (text)

Mismatch breaks validation.

Fix:

• Standardize format
• Use:

=VALUE(A2)

Or ensure list values match input format.

Step 6: Remove Extra Spaces in Source Data

Hidden spaces cause mismatches.

Fix:

=TRIM(A2)

Clean dropdown source range before applying validation.

Step 7: Fix Dynamic Range Issues

If new values don’t appear in dropdown:

Problem:

• Static range like:

A2:A10

Fix:

=UNIQUE(A2:A)

Or extend range:

A2:A1000

Step 8: Remove Merged Cells

Validation doesn’t work properly on merged cells.

Fix:

• Select range
• Format → Merge cells → Unmerge

Step 9: Fix Copy-Paste Issues

Copy-pasting data can remove validation rules.

Fix:

• Reapply validation after paste
• Or use:
  • Paste special → Values only

Step 10: Reapply Validation Rule

If nothing works, reset:

• Delete validation
• Recreate rule from scratch

This resolves hidden glitches.

Common Mistakes

• Applying validation to wrong range
• Using empty or incorrect source range
• Leaving “Show warning” instead of “Reject input”
• Ignoring text vs number mismatch
• Not cleaning spaces in dropdown values
• Using static ranges that don’t update
• Using merged cells
• Overwriting validation with paste

Pro Tips / Better Alternatives

Use Named Ranges for Dropdowns

Instead of:

Sheet2!A2:A100

Use a named range like:

ProductList

More scalable and easier to manage.

Use Dynamic Dropdowns with FILTER

=FILTER(A2:A100, B2:B100="Active")

Creates dynamic validation lists.

Use UNIQUE for Clean Lists

=UNIQUE(A2:A100)

Removes duplicates from dropdown.

Combine with Conditional Formatting

Highlight invalid entries:

=ISNA(MATCH(A2, SourceRange, 0))

Keep Validation Source Clean

Use:

=TRIM()
=CLEAN()

Clean source = reliable validation.

Bottom Line

If data validation isn’t working, fix in this order:

1. Verify correct range
2. Check validation criteria and source range
3. Enable “Reject input”
4. Fix data type mismatches
5. Remove extra spaces
6. Avoid merged cells and copy-paste issues

Most issues come from range errors and dirty data.
Fix those, and your validation will work consistently.