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:
YES!!!!!!!!!!
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.
Conclusion
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.
Sample File
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?
Our training programs start at $29 and will help you learn Excel quickly.
Hallelujiah!!!
No more macros!
I know, right!
Hi Jeff. Is this still working for you in the excel app or only the online 365 app? This was working for me for about 2 weeks a month ago but now has stopped. The online 365 app still does this.
in the past, excel always filtered my drop down list (data validation) according to what i was typing. i have recently (october 2023) installed windows 11 on a new pc build and installed office home and student 2021, now the filtering on the drop down does not work.
i have exhausted forums and google searches to find a solution but no joy.
could it be that i am using office home and student and not 365? surely that should not be the reason…
looking forward to a reply
oh and by the way, i also tried out the DDAutoComplete file, that also does not filter out on data validation… just FYI
Finally!!!!! My version of Excel has this capability as of last week. I am so excited and so are the users of the file(s) we have created.
Such a great update 🙂
Agree, we have all been waiting for this feature… seemed like such obvious need.
I’m always happy that you are on top of this stuff so I can get the highlight reels at Excel Uni!
Is the feature available for Excel in MS office 365 for Mac?
It is not working for me 🙁 I checked for updates and What’s New. I also made sure Auto Complete was checked in advanced settings. Do you know if it is or is not available currently on the Microsoft 365 Apps for enterprise version?
This change is being deployed over time, based on your Update Channel. If you are a part of an organization with an IT department, they can assist with changing the update channel based on their security policy.
It’s not working for me, either.
Great update. Thanks.
Mr. Jeff
I created a PT using Power Pivot and a date calendar last year. I’ve been refreshing it each month, and its been great. Now my data is from the new year, and when I refreshed, the PT disappeared except for the column names. I looked into it, and the dates in the date table only go to 12/31/21. Have you come across this issue? I can’t figure out how to “extend” the date table.
found it, disregard
We’re on the current channel but still don’t have it.
It looks like this may be beta only. If so, any idea when it wil be in prod?
Has anybody been able to find official documentation from Microsoft for this release?
For some reason the autotext function is not working consistently on all drop down list. Still identifying what is the reason. Its not related to whether the drop down is referred to using name, A1# or traditional A1:A2.
It’s not working for me. I’m running this version:
Microsoft® Excel® for Microsoft 365 MSO (Version 2203 Build 16.0.15028.20152) 64-bit
If this is working for anybody, please post your version here… Sad since I thought this would solve my current problem!
This function stopped working for me and my workmates around April 8th. I’ve created new workbooks and I’m getting the same results. Now, the only way the autocomplete functions is if the drop down is pulled down first.
This really reduces the functionality of the workbook. I hope they change this back!
Same here. I used to could type one letter and the list pops up, but for some reason it all of a sudden stopped doing that and now I have to pull the drop down first. Sucks.
This worked for my until two weeks ago. Has it been taken out of Office 365 Online Excel? Or is there a setting I need to update?
I was looking around the internet to figure out why it wasn’t working for me, and it appears that MS took it out (I read something about that, but I don’t remember the source anymore). I suspect that this feature caused a bug, so it was easier to take it out to (hopefully) work on it. I really hope they re-introduce this soon. Also, seeing how many people are asking this question, I would hope that MS would be a little more transparent and let people know they took it out, and why! Seriously MS, you create buzz around a new feature, deploy it for a few months… You don’t think that it will cause confusion if you just make it “disappear”???
I found a pretty decent thread on a Microsoft forum about it. It essentially says that it was in beta testing but not for all users. Some users had it, others did not. Then they did another round of beta, but not all the same people received the update.
https://techcommunity.microsoft.com/t5/excel-blog/speed-up-data-entry-and-validation-with-autocomplete-for/bc-p/3285062
Oh man! That’s legit annoying. We’ve been using drop downs in shared online Excel through Office 365 since 2017 and they’ve always had this autocomplete option for me…for 5 years! To have it removed is MORE than inconvenient. We’re in there daily multiple times a day and now I have to stop and select instead of start to type and hit enter. 🙁
hmmm… yes, been waiting for years for this. So far, nothing. Pretty much like waking up Christmas morning with the cookies and milk gone and no autofill… starting to wonder if Santa is coming?
the picture shows that the cell predicts, from the data we have validated. unfortunately, this is not the case in the sample file.
Can hardly wait for this! At least I now know why I’m not seeing it yet. Will save so much time.
For me, it works in Office 365 excel online, but not in 365 excel on my desktop station. I have to open the file online; however, it does not seem to work for some other people in my organization, and I’m puzzled trying to figure out why.
yes, it works in online mode.
does not work with desktop verion, very very aggrivating
Just tried this great idea, but doesn’t appear to have rolled into Europe yet – bit of a shame
can someone help me? this feature was working on my desktop and laptop for several weeks. and now all of the sudden no more…
why???
I made it per the instructions, and it seems like it should work, but it doesn’t.. What happens is this – all the values in the cell are there, for example apple, banana, pear, etc.. When I start typing apple, it won’t auto populate, but will fill the cell in.. for the next cell down, if I start typing apple, it WILL auto populate, since it is filled in in the cell above..
How do I get it to start auto populating from the start, without first having to input the value at least once?
This was working on my desktop version and now is not !!!! Ahhhh
Help
in others its working but not in my laptop I reinstalled it but the result is same so any body can suggest me what happen here please
Is there a way to turn it off? This feature has removed my ability to type enough information for the Autocomplete to suggest the right answer and hit the down arrow to move to the next cell. I know it’s a minor issue, but it’s really hard to break 10+ years of muscle memory.
Thanks! This helped clarify and probably saved me so much time trying to figure out how to do it on my old version excel.