'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Show Duplicate Values in an Excel Data Validation List
Question details
The user needs to display duplicate entries in a data validation dropdown list so that identical items with different underlying data remain selectable.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a data validation dropdown list where multiple items share the same name but have different underlying data, such as equipment records with varying calibration values.
- Observed behavior
- Recent versions of Excel automatically remove duplicate entries from data validation dropdown lists, causing multiple distinct records to merge into a single selectable option.
Identify the column containing your original source data and ensure you have an empty adjacent column available to create a unique helper list.
Create a Unique Helper Column for Your Dropdown List
Bypass the automatic deduplication feature by combining your source items with unique identifiers, making every dropdown entry distinctly recognizable.
By appending a unique sequence number or a specific identifier (such as a calibration value) to each identical entry, Excel will treat every item in the list as distinct. This ensures all your options appear in the dropdown menu.
Insert a new column next to your original data to act as your helper list.
In the first cell of the helper column, use a formula to combine the equipment name with a unique value. For example, type =A2 & " - " & B2 (where A2 is the equipment name and B2 is the sequence number or calibration value) and drag the formula down to fill the rest of the list.
Click on the cell in your worksheet where you want the data validation dropdown list to appear.
Go to the 'Data' tab on the ribbon and click 'Data Validation'. Under the 'Settings' tab, choose 'List' from the 'Allow' dropdown.
In the 'Source' box, select the range of your newly created helper column and click 'OK'.
Roll Back to an Earlier Version of Microsoft Office
Revert to an older Office build if your workflow absolutely requires the old data validation behavior without altering your spreadsheet structure.
How to Create a Data Validation Dropdown in WPS Spreadsheets
WPS Spreadsheets provides a fast and intuitive way to manage data validation. You can use the identical helper column technique to ensure all your distinct records appear properly in your dropdown menus.
- 1. Prepare Your Data: Open your spreadsheet in WPS Office and create your unique helper column using the '&' operator to join duplicates with unique IDs.
- 2. Select the Cell: Highlight the specific cell or range of cells where the dropdown list is needed.
- 3. Open Data Validation: Navigate to the 'Data' tab on the top ribbon and click the 'Validation' button.
- 4. Configure the Dropdown: In the pop-up window, set 'Allow' to 'List', highlight your newly created helper column as the Source, and click 'OK'.
Frequently Asked Questions
Why is Excel removing duplicate values from my dropdown list?
Recent updates to Microsoft Office 365 and Excel automatically remove duplicate entries from Data Validation lists by default. This update was designed to streamline data entry, but it can hide distinct records that share the same primary name.
Can I turn off the automatic deduplication in Excel dropdowns?
No, Excel does not currently provide a built-in toggle or settings menu to disable the automatic removal of duplicate values in data validation dropdowns. Using a unique helper column is the most reliable workaround.
Will using a helper column affect my existing formulas?
Yes. If your spreadsheet relies on functions like VLOOKUP or INDEX/MATCH based on the dropdown selection, you will need to update those formulas to reference your new unique helper column instead of the original duplicated data.
How do I hide the helper column so it doesn't clutter my sheet?
You can easily hide the helper column by right-clicking the column letter header (for example, Column C) and selecting 'Hide' from the context menu. Your data validation dropdown list will continue to function perfectly even when the source column is hidden.
