Why Excel Data Validation Lists Remove Duplicate Values and How to Control It
Question details
The user needs to understand why duplicate entries are removed from an Excel Data Validation drop-down list and how the list source type, such as a range, Table, named range, or UNIQUE formula, changes the result.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Configuring and troubleshooting a Data Validation drop-down list in a spreadsheet workbook.
- Observed behavior
- Duplicate entries disappear from the drop-down list depending on how the source list is created.
Open the workbook and select the cell that contains the Data Validation list. Go to the Data tab and click Validation to see whether the source is a normal range, an Excel Table column reference, a named range, or a formula such as UNIQUE.
Point Data Validation to a Normal Helper Range to Keep Duplicate Entries
Use this when you want the drop-down to display duplicated values exactly as they appear in the source range.
Excel and WPS Spreadsheets can behave differently depending on whether the source is an Excel Table, a named range, or a spilled array formula. A normal helper range is the most predictable way to include repeated values in a validation list.
In a new or existing sheet such as Lists, type the values in the order you want them to appear, including any duplicate entries.
Click the cell where the drop-down list should appear.
Go to the Data tab and click Validation, or Data Validation in Excel.
Under Allow, choose List. In Source, enter =Lists!$A$2:$A$20 or select the helper range directly.
Click OK, then open the drop-down and verify that duplicate entries are present.
Use the UNIQUE Function in a Helper Range to Intentionally Remove Duplicates
Use this when you want the drop-down to show only one instance of each item.
Control Duplicate Values in WPS Spreadsheets Data Validation Lists
Use WPS Spreadsheets to configure Data Validation sources exactly as you need. Keep repeated entries with a normal helper range or remove them with UNIQUE, while staying compatible with Excel files.
- 1. Open Data Validation: In WPS Spreadsheets, open your workbook and click the cell where the drop-down list should appear, then go to the Data tab and click Validation.
- 2. Choose List and set the source: In the Data Validation dialog, choose List from the Allow menu. Enter a normal range like =Lists!$A$2:$A$20 to keep duplicates, or a UNIQUE spill reference like =Helper!$D$2# to remove duplicates.
- 3. Confirm the settings: Click OK to save the validation rule.
- 4. Test the drop-down: Click the cell again and open the drop-down arrow to confirm duplicate values are shown or removed as expected.
Frequently Asked Questions
Does WPS Spreadsheets remove duplicate values from Data Validation lists?
It depends on the source type. A normal helper range can keep duplicates visible, while a UNIQUE helper range intentionally removes them from the drop-down list.
How do I make a Data Validation drop-down show only unique values?
Enter =UNIQUE(Lists!A2:A20) in a helper cell, then point Data Validation to the spill range using a reference such as =Helper!$D$2#.
Why does my Data Validation list show blanks or missing items when I use an Excel Table?
Table references can behave differently in Data Validation unless you use a named range or INDIRECT. For stable duplicate behavior, point the validation list to a normal helper range.
Can I point Data Validation directly to an Excel Table column?
Yes, but the behavior can vary by version. If you need predictable duplicate entries, create a normal helper range and reference it instead.
Is WPS Spreadsheets compatible with Excel Data Validation lists?
Yes. WPS Spreadsheets opens and saves Excel workbooks, including .xlsx files, and supports Data Validation lists, named ranges, and common list formulas.
