A few months ago, a significant update to the way that the Data Validation feature worked made using dropdown lists much more user-friendly. It seems that Microsoft might have sneaked in a further enhancement to also make lists much easier to set up in the first place.
Introduction
The ability of Data Validation to use list of values has a slightly complicated history. When first released, Data Validation lists could only use direct references to cells on the same worksheet as the Data Validation cell. From Excel 2010, references to other worksheets were made possible. However, even now references to lists on different worksheets still behave differently. If the source for a Data Validation list is a column in an Excel Table on the same worksheet, then the data validation list will adjust to changes in the number of rows in the column. If the Table is on a different worksheet, it will remain looking at the original range of cells. The solution to both of these issues is to use a named range as the list source, rather than a direct reference to cells.
More recently, Data Validation lists were made much easier to use by the addition of AutoComplete to Data Validation list dropdowns. Rather than having to scroll through an entire list to find the required match, or having to type in the full text, it became possible to start typing in the first characters of the item. The Data Validation list would then be filtered to only show matching items with the highlighted item being capable of being accepted just by pressing the Enter key.
Data Validation dropdown lists – major improvement on the way
The above article mentioned the use of the new UNIQUE() function to remove duplicates from the source list. I am fairly sure that, when the enhancement was first introduced, you did still need to remove duplicates from the source list if you didn’t want them to show up in the Data Validation dropdown. On my current version of Excel, this no longer seems to be the case. Data Validation seems to automatically show only unique items in the list, even if the source list contains multiple identical values. There is one proviso however, the built-in Data Validation unique feature seems to be case sensitive, so you might still need to use the UNIQUE() function if that is an issue for you.
We can see in the above screenshots that the UNIQUE() function doesn’t treat entries that only differ in terms of character case as being different, but Data Validation does. Where there is no difference in character case, both UNIQUE() and Data Validation remove duplicated items leaving us with just a single Puppet in each list.
I can’t think of a situation when you might want to include duplicate items in a Data Validation list but, if you did, I haven’t yet found a way in which you could easily do so. Nor have I found any information in any of the Excel What’s New documentation that this aspect of Data Validation lists has changed. If you know the answers to any, or all, of these questions, please let us know!
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.