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:

Dropdown choice list for data validation

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:

data validation allow dropdown list

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

And now for the moment of truth!

We type a letter and the choices that begin with that letter appear:

AutoComplete for the data validation dropdown list as you type a letter

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

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

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.

36 Comments

  1. Nick Mullinix on January 25, 2022 at 9:59 am

    Hallelujiah!!!
    No more macros!

    • Jeff Lenning on January 25, 2022 at 10:00 am

      I know, right!

      • Shane Archer on May 4, 2023 at 4:29 pm

        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.

  2. Teresa McKennon on January 25, 2022 at 10:07 am

    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.

  3. Bill on January 25, 2022 at 11:26 am

    Agree, we have all been waiting for this feature… seemed like such obvious need.

  4. Sandra on January 25, 2022 at 2:31 pm

    I’m always happy that you are on top of this stuff so I can get the highlight reels at Excel Uni!

  5. Christiane Ekness on January 25, 2022 at 3:02 pm

    Is the feature available for Excel in MS office 365 for Mac?

  6. Carrie on January 25, 2022 at 6:13 pm

    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?

    • Jeff Lenning on January 26, 2022 at 9:02 am

      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.

    • Abbott Katz on March 21, 2022 at 5:06 pm

      It’s not working for me, either.

  7. Genevieve Andrade on January 25, 2022 at 9:52 pm

    Great update. Thanks.

  8. Kurt on February 1, 2022 at 2:05 pm

    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.

    • Kurt on February 1, 2022 at 3:09 pm

      found it, disregard

  9. James on March 29, 2022 at 6:02 pm

    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?

  10. Giel Verbeeck on April 7, 2022 at 3:27 pm

    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.

  11. Brian L on April 7, 2022 at 10:24 pm

    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!

  12. Charles on April 13, 2022 at 1:45 pm

    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!

    • Brittney Williams on April 19, 2022 at 12:32 pm

      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.

  13. Kristina on April 14, 2022 at 10:12 am

    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?

  14. Brian L on April 14, 2022 at 10:32 am

    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”???

  15. Charles on April 14, 2022 at 3:49 pm

    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

    • Kristina on April 14, 2022 at 3:54 pm

      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. 🙁

  16. Billl on April 29, 2022 at 12:58 am

    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?

  17. muhammad abbas ahmed on August 18, 2022 at 7:34 am

    the picture shows that the cell predicts, from the data we have validated. unfortunately, this is not the case in the sample file.

  18. Meesh on August 29, 2022 at 6:52 pm

    Can hardly wait for this! At least I now know why I’m not seeing it yet. Will save so much time.

  19. Sonia on August 30, 2022 at 7:29 pm

    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.

  20. Kiran on October 13, 2022 at 5:46 am

    yes, it works in online mode.

  21. christi on January 10, 2023 at 12:13 pm

    does not work with desktop verion, very very aggrivating

  22. David Shannon on January 12, 2023 at 8:32 am

    Just tried this great idea, but doesn’t appear to have rolled into Europe yet – bit of a shame

  23. Ian on April 25, 2023 at 5:38 am

    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???

  24. mark s on April 28, 2023 at 3:47 pm

    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?

  25. Adam bonham-Carter on May 8, 2023 at 12:42 am

    This was working on my desktop version and now is not !!!! Ahhhh

    Help

  26. deepak on August 15, 2023 at 6:11 am

    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

  27. Nick Savelesky on September 25, 2023 at 6:10 pm

    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.

  28. Vj on November 5, 2023 at 12:00 pm

    Thanks! This helped clarify and probably saved me so much time trying to figure out how to do it on my old version excel.

Leave a Comment