Data Validation with unique values

Copper Contributor


I have a question on Data Valitation.

I have a list with duplicated values on it. I do data validation with this list to get a drop list. When done with one computer I get a drop list with only unique values out of that list (that is what I want), but if I do the same - same file - in another computer, I get a drop-list with duplicated values (many lines with same text). Do you know what the reason would be? Thanks


9 Replies


Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?


The behavior you are describing with Data Validation drop-down lists can occur due to the settings applied on different computers or the specific version of Excel being used. Here are some factors to consider to ensure that you get unique values in the drop-down list on all computers:

  1. Data Validation Settings:
    • Ensure that the Data Validation settings are consistent on both computers. The settings should be configured to allow only unique values if that is what you desire.
  2. List Source:
    • Verify that the list source used for Data Validation is the same on both computers. If the source data contains duplicates, the drop-down list will reflect those duplicates.
  3. Excel Version:
    • Different versions of Excel may handle Data Validation differently. Ensure that both computers are using the same Excel version, and if not, check for any version-specific behavior that might affect Data Validation.
  4. List Range:
    • Double-check the range specified for Data Validation. If it includes extra rows or duplicates on one computer, it can result in different drop-down lists.
  5. Named Ranges:
    • If you are using named ranges for Data Validation, verify that the named ranges are consistent and do not include duplicates.
  6. Workbook Compatibility Mode:
    • Ensure that the workbook is not opened in Compatibility Mode, as this can affect how Data Validation works.
  7. Data Formatting:
    • In some cases, formatting or styles applied to cells can affect how Data Validation displays lists. Make sure cell formatting is consistent.
  8. Excel Add-ins or Extensions:
    • Check if there are any Excel add-ins or extensions installed on one computer that might be altering Data Validation behavior.
  9. Excel Options:
    • Compare the Excel options and settings between the two computers, paying special attention to any settings related to data or lists.
  10. Office Updates:
    • Ensure that both computers have the latest updates and patches for Microsoft Office. Sometimes, updates can resolve compatibility issues.

By addressing these factors, you can help ensure that Data Validation drop-down lists display unique values consistently across different computers. If the issue persists, it may be helpful to share specific details about the Data Validation settings and the Excel versions being used on both computers for more targeted assistance. The text and steps were edited with the help of AI.


My answers are voluntary and without guarantee!


Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

Thanks, where do I see the settings to "allow only unique values"?


here you have 2 pictures. I am doing exactly the same on both computers




If you select the cell with the drop-down, then click Data Validation on the Data tab of the ribbon, what is the Source on the first computer, and what is the Source on the second computer?

it is the same complete list in both computers.
I assume that it has to do something with the excel version, as I send the same file from one computer to another, and it behaves differently


What are the versions of Excel on the two computers?

Computer 1 - Version 2309 (16827.20130) (shows unique values)
Computer 2 - Version 2303 (16227.20318) (shows same values repeated)
I checked same file with a thrid computer MAC - with version 16.77.1 (23091703) and shows also values repeated).
Computer 1 is the only one that shows correctly only unique values in the drop down list




It looks like Microsoft quietly introduced this in Excel for Windows in the August 2023 update, together with the AutoComplete feature in data validation drop-downs. That's why it works in version 2309 but not in 2303. It'll probably be released for Mac pretty soon.