Excel Data Validation Drop-Down Removes Duplicate or Blank Rows
Question details
The user needs to understand why an Excel data-validation drop-down removes duplicate values or blank rows and how to check whether the behavior is controlled by a newer filtering feature.
- Product
- Microsoft Excel data validation
- Device & OS
- not provided
- Scenario
- A user opens or searches a data-validation list and expects to see duplicate entries or blank rows from the source range.
- Observed behavior
- Excel may automatically filter duplicate values and blank rows from the data-validation drop-down, especially when users type into or search the list.
Check the Excel version and build under File > Account, then inspect the data-validation source range under Data > Data Validation. Confirm whether the source is a normal range or a structured table and whether the list changes frequently.
Check Excel version and data-validation filtering settings
Use this approach when the drop-down recently changed behavior after an Excel update or when searching the list filters entries unexpectedly.
Newer Excel data-validation behavior may dynamically filter list entries as users type. This can remove duplicate values and blank rows from the displayed results even when they remain in the source range.
Open Excel, select File > Account, and check the Product Information and Office Updates sections for the installed version and build.
Select the affected cell, go to Data > Data Validation, and review the Source field. Verify that the referenced range contains the expected duplicate values and blank rows.
In the Data Validation dialog and Excel options, look for any setting related to searchable, dynamic, or filtered drop-down lists. If your version provides a disable option, turn it off and test the list again.
Open the drop-down and compare the results before and after typing in the list. This helps determine whether dynamic text filtering is causing the entries to disappear.
Report the behavior to Microsoft and review the list structure
Use this option when no control exists for the filtering behavior or when the source range does not explain the missing entries.
Use WPS Spreadsheets as a Lightweight Alternative for Data Lists
If the issue is specific to Microsoft Excel’s newer data-validation filtering behavior, WPS Spreadsheets offers a free, lightweight alternative with a familiar interface and strong compatibility for opening, editing, and saving Excel workbooks.
- 1. Open the workbook in WPS: Launch WPS Office, select Spreadsheet, and open the existing .xlsx workbook from the Home screen.
- 2. Review the validation list: Select the target cell and use the Data tab to inspect the data-validation rule and its source range.
- 3. Test and save the workbook: Open the drop-down, verify the displayed values, then choose Menu > Save As and retain the Excel Workbook (.xlsx) format when sharing the file.
Frequently Asked Questions
Why does Excel hide duplicate values in a data-validation drop-down?
A newer dynamic filtering behavior may remove duplicate entries from the displayed list, particularly when the user types or searches inside the drop-down. Check the Excel version and build under File > Account.
Why are blank rows missing from my Excel validation list?
Excel may filter blank rows automatically in the drop-down display. Open Data > Data Validation and inspect the Source range to confirm that the blank rows still exist in the referenced data.
Can I disable dynamic filtering in Excel data validation?
This depends on the Excel version and update channel. Check Data > Data Validation and Excel options for a setting related to searchable or filtered drop-down lists. If no option is available, submit feedback through Help > Feedback or contact Microsoft Support.
Would converting the source range to a table help?
A structured table can make a frequently changing validation list easier to maintain and may provide more consistent source-range behavior. Select the list, choose Insert > Table, and then update the validation Source field.
Does WPS Office fix Excel’s data-validation filtering behavior?
WPS Office does not change how Microsoft Excel handles its own dynamic filtering feature. However, WPS Spreadsheets can serve as a free alternative for opening, editing, testing, and saving compatible .xlsx workbooks.
