AutoComplete for Data Validation Dropdown Lists
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.
How to use AutoComplete for dropdowns
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:
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:
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!
Do I Have AutoComplete for Dropdown Lists?
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.
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.