Function Problems

How to Find Excel Cells That Use Data Validation Lists

Maira Mehtab Sep 20, 2026 869 views

Question details

The user wants to locate cells containing data validation lists across multiple worksheets to determine which lists are actively used and which can be safely deleted.

Product: Excel
Device & OS: not provided
Scenario: Managing and cleaning up multiple data validation lists distributed across complex worksheets.
Observed behavior: The user needs to inspect the validation settings on various cells to identify active lists and safely remove lists that are no longer referenced.

Before you start

Before attempting to locate or remove any data validation lists, ensure your worksheet is unprotected so you can freely access the Data Validation settings and make necessary changes.

Solution 1Recommended

Inspect Cells via the Data Validation Tool

Use the built-in Data Validation tool on the ribbon to check the source of the validation list for specific cells.

If you already know roughly where your data validation lists are, you can inspect them individually to see which source data they are pulling from.

Select the Target Cells

Highlight the cells or range in your worksheet that you suspect contain data validation lists.

Open Data Validation

Navigate to the 'Data' tab on the ribbon menu and click on the 'Data Validation' icon.

Review the Source List

In the Data Validation dialog box, check the 'Settings' tab. Look at the 'Source' field to see which list or range is actively being referenced by the cell.

Solution 2

Highlight All Cells with Data Validation

Use the 'Go To Special' feature to quickly highlight every cell in a worksheet that contains data validation, making it easier to check them all at once.

Easily Manage Data

Manage Data Validation Effortlessly in WPS Spreadsheet

WPS Spreadsheet offers intuitive tools to find, edit, and clean up data validation rules across your worksheets. It provides full compatibility with Microsoft Excel formats, ensuring your drop-down lists work seamlessly without format loss.

1. Open Your File in WPS: Launch WPS Spreadsheet and open your Excel workbook.
2. Find Validated Cells: Press Ctrl+G, click 'Special', and select 'Data validation' to automatically highlight all cells with drop-down lists or rules.
3. Check Data Sources: Go to the 'Data' tab and click 'Validation' to inspect the active lists, clear rules, or update the source ranges easily.

Easily highlight and inspect data validation lists using Go To SpecialFully compatible with Microsoft Excel (.xlsx) formats and validation rulesLightweight, fast, and completely free to useIntuitive interface for managing complex data sets and drop-down menus

microsoft office alternative - wps office

Frequently Asked Questions

How do I remove a data validation list from a cell?

Select the cell containing the drop-down list, go to the Data tab, click Data Validation, and select 'Clear All' in the bottom left corner of the settings tab. Click OK to remove the validation.

Can I find all cells that use one specific validation rule?

Yes. Select one cell that uses the specific validation rule you want to match. Press Ctrl+G, click Special, select 'Data validation', and then choose the 'Same' radio button. This will highlight all cells sharing that exact rule.

Why is my data validation list not showing the drop-down arrow?

The drop-down arrow might be disabled in the cell settings. Select the cell, open the Data Validation dialog box from the Data tab, and ensure the 'In-cell dropdown' checkbox is ticked.

Excel & Data Problems