Dropdown choice list for data validation" width="199" height="285" />Microsoft just announced an enhancement in Excel desktop that enables autocomplete for data validation dropdown lists … YES! I have been waiting for this capability for literally decades … and it is finally here 🙂
Note: this feature is available in both Excel online and desktop. However, at the time of this writing, it is available on the desktop in O365 licenses only. Depending on your update channel, you may have access to it immediately or over time as the update is deployed through the various update channels.
Let’s say we want to allow our user to select a department from a dropdown list. We would begin by creating a list of the departments in a range, like this:
Dropdown choice list for data validation" width="199" height="285" />
Next, we would create a dropdown list in the desired input cell. So, we select the input cell and click Data > Data Validation. At the time of this writing, the data validation command icon looks like this:
In the resulting Data Validation dialog, we set the Allow option to List. Then, in the Source field, we select the range of cells that contains our list items (our departments in B10:B20). The resulting Data Validation dialog looks like this:
When we click OK, we can see that the input cell now has a little icon that indicates it contains a dropdown list:
Data validation dropdown list in input cell" width="372" height="104" />
And now for the moment of truth!
We type a letter and the choices that begin with that letter appear:
If we continue typing additional letters, the choices will be filtered down accordingly.
We can hit Enter to select the one we want.
This small update causes Excel dropdowns to function and behave as we would expect. And it will certainly save time!
Probably the fastest way to determine if your version of Excel includes this is to create a dropdown list and test it. However, you can also click File > Account > What’s New. This opens the What’s New panel on the right. Scroll down a bit and look for this note:
Facilitate your data validation with AutoComplete for dropdown lists
Dropdown lists are a handy way to make data entry and validation more efficient in Excel. We’ve now added AutoComplete functionality, which automatically compares the text typed in a cell to all items in the dropdown list and displays only the items that match. You’ll spend less time scrolling through lists, dealing with data validation errors, or writing complex code to handle this task.
Over the many years I’ve been using and teaching Excel, this is perhaps one of the most frequently requested enhancements. So, I’m thrilled it is now part of Excel.
