Speed up data entry and validation with AutoComplete for dropdown lists in Excel for Windows
Published Jan 31 2022 09:00 AM 58.7K Views
Microsoft

At Excel, customer focus is at the core of what we do. We appreciate your feedback on adding autocomplete to data validation dropdown lists in Excel for Windows. Your Excel Community votes ensured this was one of the next in line to release after smooth scrolling. Thank you for keeping us honest and we hope this meets your expectations.

Today, we are excited to announce that speeding up data entry and validation with AutoComplete for dropdown lists is now available in Excel for Windows in the Current Channel!

 

What is AutoComplete for dropdown list?

AutoComplete algorithm for dropdown list matches the string you type in the dropdown list cell with words from items in the dropdown list and then shows only the matching list items. As you type more characters, the dropdown list contracts, and conversely, when you remove characters, it expands. Matching words can be from anywhere in the list item's string – at the start, middle, or end. Also, the dropdown list excludes blank items from the Data Validation dropdown list.

 

(Photos Courtesy of NASA)(Photos Courtesy of NASA)

 

 

AutoComplete for dropdown list helps speed-up discovery of the right Data Validation values at first attempt, and in turn, reduces time spent scrolling through the dropdown list, dealing with Data Validation errors, or writing complex code to enable it. And includes excluding duplicates from the dropdown list.

 

Coming soon 

We plan to expand our rollout to Excel Mac and Mobile endpoints next.

 

Availability and Feedback

AutoComplete for Data Validation drop-down list is available in Current Channel with version 2306, build 16.0.16501.20004 or later. Please keep in mind features are enabled gradually to more and more users over time as we build confidence in the success of a feature working properly. And so, this feature may or may not be enabled on your computer immediately. When you do see it enabled, please try it out! We’d love to hear from you whether this meets your expectations or not. Please send us feedback!

 

Also, do you have an idea to make Excel better? We'd love to hear about it. Go to the Excel forum in Microsoft Feedback to submit your idea or vote for other ideas. You can also click the Feedback button in Excel and choose "I have a suggestion".

 

Want to know more about Excel? See What's new in Excel and subscribe to our Excel Blog to get the latest updates. Stay connected with us and other Excel fans around the world – join our Excel Community and follow us on Twitter.

 

Thank you!

122 Comments

That's great improvement, thank you. To clean list in background that will be nice if with duplicates you remove blanks as well.

Copper Contributor

What a relief!  Looking forward to using this.  Thank you!

Copper Contributor

Thank you for this feature !

I am on the beta channel, version 2022 (build 14934.20010) and I don't have it yet. I hope it will come soon :)

Copper Contributor

Info: Microsoft 365 Personal (CZ/EN), Beta Channel, version 2203, not available...

Webpage https://docs.microsoft.com/en-us/officeupdates/beta-channel does not mention any changes in Excel.

 

Update 13. 2. 2022: Autocomplete is back. For Czech language: The search is performed only from left to right from the first character. Thanks

Update 23. 2. 2022: I think, that searching could be the same as in Data / Filter textbox.

 

And please! Step 2: Stop rewritting Data / Validation with Ctrl + V.

Copper Contributor

Estuvo funcionando bien pero desde la ultima actualización no funciona en mi canal beta

 

Copper Contributor

Substring match does not appear to be working for items that contain only one word  

Copper Contributor

In my experience, the search results only include words that begin with the search string. One-word expressions appear first in the results, followed by multi-word expressions. From the point of view of regular expressions, it would be appropriate to search anywhere in the items.

Copper Contributor

Does anyone know how long this will take to make it to the Current Channel? I am in the process of building a tool and would rather not rely on ActiveX controls to get the autocomplete feature...

Copper Contributor

Yes, when will this be out of beta? I'm keeping a project on hold for this because I have a list of almost 3,000 items for my drop-down cell.

Copper Contributor

I also have a project on hold... Any update on availability? Thank you

Brass Contributor

@SKeeeches & @RobCalc1997 

You can try using a free Excel add-in.
Just google "Excel add-in called Search deList".

 

Copper Contributor

@Prash Shirolkar I am new to Office Insider Program and I want to explore the new feature - Auto complete for Data Validation. But I could not see in my Excel Even though the Excel was updated with latest version 2204 (Build 15121.20000). Is this feature removed or changed in the latest Build?

 

Note: After the update I done restart my machine multiple times to check if the functionality enabled. 

 

My Laptop is in Windows 11. I haven't tried in Windows 10 yet.

 

Please assist.

Copper Contributor

Yes kpravin89, you are right. This functionality is missing in build 2204 (Office 365 CZ/EN, Insider, Beta Channel). I don't know why.

Copper Contributor

On my side it is not working on my windows 11 desktop (last beta version)

But it has never stopped working on my windows 10 laptop (2203 (15028.20022))

New functionality is deployed to insiders gradually. If you have latest Beta build that only means the code for the functionality is inside your build. If you don't see new functionality that means such code is not activated. Usually up to half of insiders have activation with first flight. The rest could receive it in few weeks or in few months, it very depends on stats collected from first flight. After full Beta coverage it goes to Current (Preview) channel and after that for GA, channel by channel.

Which Windows version usually doesn't matter.

 

That is here Speed up data entry and validation with AutoComplete for dropdown lists in Excel for Windows (office...

image.png

Copper Contributor

Hi

 

Has there been a recent change to this? You used to be able to type and the list would automatically show and search as you typed.

 

Now, it doesnt seem to work as it wont automatically show the list when you start typing in the cell. You have to click the list down arrow, to open the list, then start typing out the string to refine.

 

Its not much of a big deal, but its an extra click and a really frustrating change.

 

Matt

 

@MattH75 , as it is mentioned in previous post feature is deployed gradually and could be pulled out. That what happened here. Let assume initially it was distributed to 50% of insiders. For a while it was pulled out, thus nobody had it. After the fixing (I don't know what was actually done) it was again distributed to 50% of insiders. The point is first 50% and second 50% are not necessary the same people.

It looks like you didn't win the lottery second time.

Silver Contributor

Interesting.  I have just tried this with species of pet.  Converting to a data type meant that the cat became 'domestic cat'.  Looking for the animal beginning with 'c' still worked however.

Copper Contributor

@Sergei Baklanthanks for the explanation. My users are all grumbling because the feature has been removed. We are looking forward to this being re-implemented.

@Charles_Carrado , I'd do not recommend to use Beta functionality in production. It could be dropped out at any time, functionality behaviour could be changed, parameters could be added or their order changed. Nothing is in stone as far as we have functionality only on Beta. That's only to play on your own and give feedback to Microsoft what you like and what don't.  

Copper Contributor

@Sergei Baklan Thanks for your response, but guess what?? It's now working again. We are not on the beta either.

 

 

Copper Contributor

MattH75: I can confirm this... my AutoComplete is now working :sparkles::party_popper::fireworks::balloon:

Copper Contributor

I hope to join the party someday. 

Copper Contributor

SKeeches: that sucks and sorry to hear; wish I had the power to extend an invite :)

Copper Contributor

"That's only to play on your own and give feedback to Microsoft what you like and what don't."

 

I'm sorry, Sergei, but the development has been going on for several months, and most of us have had the opportunity to try the tool for just a few minutes. No, no, yes, no, no - this is what the availability of the tool in beta looks like. We have been waiting for this tool in Excel for 20 years. Even if we see it officially (maybe in a year, maybe not at all), Ctrl+V will destroy Data Validation? It seems to me that I am not a developer, an Excel user, a lecturer, but a clown. And the same goes for an unavailable calendar in Excel. We obviously don't care about Microsoft. Excel Voice is dead and this is a toy store. Probably not having to deal with user opinions. Should we pay attention to products like OnlyOffice, WPS Office and others? I am sorry for my English.

Copper Contributor

Is this ever going to work? I'm on the beta, it worked for a week in Jan or Feb & then disappeared. Three rounds with tech support, multiple pleas to the community, 2 re-installs, & 3 months later the dang thing still doesn't work. What the frick is going on??  Is there a known issue? Is it being worked on? Is there a workaround? And more importantly, how the heck do you go about getting a straight answer from someone?

Copper Contributor

Runser: see my post here: https://answers.microsoft.com/en-us/msoffice/forum/all/excel-online-dropdowns/34561567-977a-4339-8dd...

 

It won't really help you, but might shed some light on the situation... 

Copper Contributor

Wow. Why can't they just tell people that? Clearly their own tech support personnel don't even know or they wouldn't have spent so many hours trying to fix it. Thanks, Kristine2021!

Copper Contributor

I know right? Very frustrating... anyway, glad I could sorta help :lol:

Copper Contributor

Thanks to everyone here!

 

Here is my feedback: I love the auto completion of the dropdown. The 250-300 users on my team love it to!

That said, it is working again on our sheets.

Copper Contributor

Simplemente dejo de funcionar, versión beta:  v:2206 - 15227.20000

Copper Contributor

Beta Channel Insider, 2206, Czech: STILL NOT AVAILABLE. And MVP's are spreading the word, how is Microsoft finalizing this tool? Are you kidding us???

Iron Contributor

Now shows unique items if the source contains duplicates

Can we please have a checkbox - that we can check to enable / disable this

 

Cheers

Sam

Copper Contributor

Are there any updates as to when this will be released or how to go about receiving the build?

Copper Contributor

@Noravanspey Below I have a screen capture of my Excel version so you can compare it to yours. FYI, autocomplete is working lovely in my spreadsheets.

 

Capture241.jpg 

 

And here is a screen capture of my Windows version, again so you can compare:

 

Kristine2021_0-1654093041288.png

 

Not that this answers your questions directly... but gives some context maybe...

 

Best,

Kristine

 

 

 

Copper Contributor

@Kristine2021  I have that same version and the Auto Complete is not working in a list... weird

Copper Contributor

The solution I found to get around it until the feature is more widely released is an add-in called Search de List.

 

An alternative that is very simple is to use the "Search de List" free add-in so you can use your desktop app. After you install the add-in, while using a data validation dropdown list, you can press ALT+right arrow and a searchable box pops up. I've attatched the add-in below. Download it, then in Excel, go to FILE-OPTIONS-ADD INS- MANAGE EXCEL ADD INS - BROWSE - go to your downloads and select the file to install. https://www.mrexcel.com/board/threads/i-created-an-excel-add-in-called-search-delist-to-create-searc...
Copper Contributor

@Blauman That IS weird... hope that Microsoft solves that for you (and many others) soon. Even though the auto complete is working for me, I feel that I could easily loose it again given the number of people that still don't have that feature yet returned to them.

Copper Contributor

I hope Microsoft brings this feature back.

Copper Contributor

I have Excel 365 and still not able to make use of this feature!

Brass Contributor

I imported my Excel to Google Sheets. The ability to type and search within a look up/drop down cell worked out of the box!
I subscribed to a business Google account for my team and my security husband is wildly upset. 

I have a job to do and Microsoft is a dinosaur!! 

Copper Contributor

Lovely feature but still not in the desktop 365 version, only online. SINCE january! It's actually amazing how long time this can take for Microsoft to deploy

@kumlin_m , it depends on which channel you are. If semi-annual that could take more than a year. 

Copper Contributor

Google Forms is the relief after struggling in Excel for so long.

Silver Contributor

@Sara Laidlaw 

Don't forget that data validation / dropdowns is a very minor part of Excel.  I agree the new functionality is such a huge improvement that one wonders why it wasn't like that from the outset, and I also appreciate that data input might form a major role in shaping your needs.  For me, it is more important that I can program solutions to a wide range of problems and Excel is beginning to meet that requirement rather well.

Copper Contributor

Hi, is there any news about the official release? It's been almost a year since its beta release, from what i can see, and still nothing. I really hope they will implement this feature since it would be pretty useful for me. I've tried it on excel online and it works like a charm!

Copper Contributor

Is there a way to get a version of this. My sales team uses a few differnect very long lists to scroll through each time they enter prodcut. This will be a great enhancement for us one fully release.

Copper Contributor

@Prash Shirolkar 

 

Hi Prash...

I'm a long-time Excel user / developer and started using Excel 365 only since yesterday!

Have already found tons of improvements (from Excel 2016)!

 

Is this particular feature stuck in the release pipeline for non-Insider users?

It would be great to have this feature soon, as the default (without AutoComplete) is a real pitb...  ;)

 

Cheers!

KV

Brass Contributor

As an alternative, try using "Search_deList_v2.1", it's a free Excel add-in, it works on Excel 2007 or later.

Its function is to speed up the search in the data validation list. In any cell that has data validation (with type List) pressing ALT+RIGHT will open a User Form with a combobox. You can type multiple keywords (separated by a space) in the combobox to search items on the list.

By using this add-in, you don't need to have macro & the combobox in your workbook, so you can save your files as .xlsx. And it work on any open workbook.

 

This new version, i.e. v2.1, has some additional features, some of them:
Several ways to search, like using AND or OR or LIKE operator , with or without keyword order.
Sort the list by original order or ascending order.
Widen or shorten the combobox width at run time.
Insert multiple entries into the cell.

image Search deList 2.jpg

You can find it here:
https://www.mrexcel.com/board/threads/i-created-an-excel-add-in-called-search-delist-to-create-searc...

Copper Contributor

Hey, it's already 2023 and I wanted to know if the feature has rolled out yet or when will it be rolled out? Can somebody help?

Co-Authors
Version history
Last update:
‎Sep 01 2023 05:03 PM
Updated by: