Excel Data Validation: Finding and Managing Dropdown Lists

Finding Validation on Current Sheet

Data tab → Data Validation → Circle Invalid Data

This circles cells with values that violate their validation rules. But it doesn't show you where validation exists — only where it's been violated.

To see cells with validation: Ctrl+G → Special → Data Validation → All

This selects all cells on the current sheet that have any data validation. But you can't see what the validation rules are from the selection.

Viewing Validation Details

Select a cell → Data tab → Data Validation

The dialog shows the rule type, criteria, and any input messages or error alerts. But you can only view one cell's validation at a time.

Common Data Validation Types

• List: Dropdown from a range or comma-separated values
• Whole number/Decimal: Numeric ranges
• Date/Time: Date ranges
• Text length: Character limits
• Custom: Formula-based validation

Validation Problems

Broken source ranges: List validation pointing to deleted or moved ranges. The dropdown appears empty or errors.

Inconsistent rules: Same column has different validation in different rows — often from copy-paste accidents.

Hidden validation: Important dropdowns that users don't know exist because there's no visual indicator.

The Cross-Sheet Challenge

Like conditional formatting, data validation has no workbook-wide view. To audit all validation, you must check each sheet individually using Go To Special.

There's no way to get a list of “all dropdown lists in this workbook with their source ranges.”

Official Resources

• Apply data validation — Microsoft's validation guide
• Find validation cells — locate rules in workbooks