Function Problems

How to Stop Excel Data Validation Lists from Removing Duplicates

Olivia Miller Oct 12, 2026 869 views

Question details

The user wants to keep all original options, including duplicate entries and blanks, fully visible in an Excel data validation drop-down list.

Stop Excel Data Validation Drop-Downs from Removing Duplicate Items

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a drop-down list using Data Validation where the original duplicate items need to be preserved and selectable.
Observed behavior: The Excel data validation drop-down list automatically filters the source data to display only unique values, hiding any duplicate entries and blank spaces.

Before you start

Ensure your original source data is organized in a single column or row, and determine if you have space in your worksheet to create a hidden helper column.

Solution 1Recommended

Create a Numbered Helper List with a Formula

Make each item distinct by appending a dynamic row number, preventing the software from reading them as exact duplicates.

Since the software automatically filters out exact text matches, the most reliable workaround is to make every entry technically unique. By combining the original text with a row number using the CONCAT function, you can keep duplicates visible while maintaining an organized look.

Insert a helper column

Insert a new, blank column directly next to your original data list.

Enter the CONCAT formula

Assuming your data starts in cell A2, click the first cell of your new column and enter the formula: =CONCAT("(",ROW()-1,") ",A2)

Fill down the list

Click and drag the fill handle (the small square at the bottom-right of the cell) down to apply this formula to all items in your list. Each entry will now have a unique number prefix.

Apply to Data Validation

Select the cell where you want the drop-down menu. Go to the Data tab, click 'Data Validation', choose 'List' under Allow, and select your newly created helper column as the Source.

Create a Numbered Helper List with a Formula

Dynamic Updates: This numbered helper list will automatically update whenever you change or add values in your original source column.

Solution 2

Add Invisible Trailing Spaces to Duplicate Entries

A quick manual workaround for small lists without using formulas by making duplicates technically unique with invisible spaces.

Easy Data Management with WPS Office

Manage Drop-Down Lists Seamlessly in WPS Spreadsheet

WPS Spreadsheet offers a highly compatible and user-friendly interface for creating and managing data validation lists. You can easily use formulas to handle duplicates, build helper columns, and streamline your entire data entry workflow.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the source data.
2. Create a helper column: Use the CONCAT formula in an adjacent column to add unique prefixes or suffixes to your list items.
3. Access Data Validation: Highlight the cell for your drop-down menu, navigate to the 'Data' tab on the top ribbon, and click 'Validation'.
4. Set the list source: Under the Settings tab, select 'List' from the Allow drop-down menu, highlight your helper column as the Source, and click OK.

100% compatible with Microsoft Excel file formats (.xlsx, .xls)Robust data validation and formula support including CONCAT and VLOOKUPFree, lightweight, and fast alternative for everyday spreadsheet tasksFamiliar interface requires zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does the spreadsheet automatically remove duplicates in drop-down lists?

Spreadsheet applications are designed to simplify data entry by automatically filtering out exact duplicates in a Data Validation list. This ensures users select from unique categories, reducing clutter, but it can be frustrating if you specifically need identical items for sequential tracking.

Will adding a helper column affect my existing formulas?

Adding a helper column will not break your existing formulas as long as you insert it carefully outside of your main dataset array. It is best practice to place helper lists at the far right of your dataset or on a separate, dedicated 'Settings' worksheet.

How do I hide the helper column from other users?

You can right-click the header letter of the column containing your helper list (for example, Column D) and select 'Hide' from the context menu. The Data Validation list will continue to function perfectly even if the source column is completely hidden from view.

Excel & Data Problems