SOLVED

Excel AutoComplete not working with Data Validation Lists even after following all fix suggestions

Copper Contributor

Hi,

I would appreciate help with AutoComplete in Excel.

 

Problem:

- Data Validation List created does not show suggestions when typing valid entries in the dropdown cell

- Clicking on the dropdown shows the list but typing while the list is dropped down does nothing

- Just typing directly into the cell types the characters but does not provide suggestions for matching entries to choose from

- Case doesn't seem to matter - inputting the exact case of the text or all in lower case makes no difference

 

Things tried:

- Checked that "Enable AutoComplete for cell values" and "Automatically Flash Fill" are checked (tried while removing "Automatically Flash Fill" turned off but no difference)

- Made sure "Ignore Blank" and "In-cell Dropdown" are checked

- Made sure there are no headers above the list data linked to the dropdown

- Opened new sheet and tried from zero with no luck

- Tried with both .xlsx and .xlsm files

 

I'm at a loss here.  Any recommendations appreciated..

 

Thank You,

 

Brent

28 Replies
best response confirmed by BrentM500 (Copper Contributor)
Solution
Excel desktop will get those features you are asking for in the near future. Currently, this only works in Excel on-line and on the Insider version of Excel.

@Jan Karel Pieterse - thanks so much for the response.  That definitely explains things..  Hopefully it will come soon - a long time pending...  I've had to use Form Controls vs Active X controls for now because my app has to work on MAC.  But when AutoComplete works I will migrate the dropdowns to Data Validation lists..  Thanks again..

So here has been my strange experience with this. I and a colleague within my organization are working from the same shared Excel worksheet. We both have the same version of Excel and we both have the workbook open in the desktop version of Excel. I have access to the AutoComplete feature with the Data Validation lists, but my colleague does not.
This is because features may be gradually distributed to users. A random part of the users on a certain build will get feature A, the others will not. The more certain MSFT is about an update, the higher the percentage that gets it first time round.

Your colleague should see this feature enabled in due course.

Hi @Jan Karel Pieterse 

I signed up for the Insider version of Excel (https://insider.office.com/en-us/join/windows) and chose the Beta Channel. Re-initiated Excel and now the Form Control dropdown works like a charm!

Thanks so much for your help..

@BrentM500I had the autocomplete function working on my machine and it has suddenly just stopped working. This is sooooooo frustrating, I wish that excel users could be taken seriously with a stable piece of software to work with.

I'm not on the Insider version, it was working perfectly for me, it started working for a friend of mine, my son can't get it to work and it has just stopped working for me in the last 24 hours.
Any update on why they disabled the feature and if they will bring it back? It was very useful, especially for colleagues that did not work with excel as much..
My ability to use autocomplete dropdowns also disappeared as of yesterday. Hoping they reinstate this feature soon.

Hi all,

 

Interesting to see what you guys are seeing here..

 

Looks like there's varying results due to MSFT being in the middle of rolling the Beta functionality out slowly instead of all at once.

 

I just checked and mine is working OK.  I'm on Version 2305 (Build 16414.20000) - Beta Channel.

 

I need my app to work on my sister's MAC Excel, which I don't know yet if the MAC OS version will have this functionality and I can't find anything about MAC OS users being able to participate in the Beta Channel.

 

Thanks,

 

Brent

I'm on Version 2303 (Build 16227.20280 Click-to-Run) and the autocomplete was working fine, then it just stopped yesterday. No updates are available and none have been done to explain what happened. It's really flaky performance from Microsoft, and I have to pay for support to have them explain why they can't provide something more stable.

@Jan Karel Pieterse 

Hello Jan, do you have any approximate date or time frame that Microsoft will bring this feature back? It's so very useful for a range of business applications and for when designing data entry sheets for users with low excel skill/knowledge.

Thanks.

The AutoComplete for drop-down lists feature on Excel for Windows was in the process of rolling out to all Current Channel customers. As we roll it out, we also ensure things are working smoothly before we expand our rollout to larger numbers of users. Sometimes, based on user feedback, we find things aren't working as expected. This is rare. That said, we have confirmed one such issue, so we have rolled back (a.k.a. turned off) AutoComplete for drop-down lists for all our Current Channel users.

We apologize for any inconvenience this is causing you. We are working hard to fix the issue as we speak in order to restart rolling out AutoComplete for drop-down lists in Current Channel as soon as we can.

thanx,
Prash

@Prash Shirolkar 

Thanks Prash.

Good to know it will be rolled out ASAP once the bugs are fixed. It's a really neat feature and no more messy crazy VBA or formulas or helper columns to get this working.

Cheers, have a good day.

This feature is available once more with this week's Microsoft 365 update.

@muibeta  It is extremely frustrating.  A peer helped me develop a worksheet and he evidently had this option.    Not knowing the history, we rolled this out to a broad audience with pomp and circumstance.  As users accessed the worksheet, we discovered it worked for some and not for others.  

 

I updated this morning and still don't have this functionality so I guess it is still not rolled out for all users.

fully updated (just tried, tells me I have the latest version) and no autocomplete.

 

Because autocomplete is such a bleeding edge option for a thirty eight year old application... like, how much have MS made selling Excel over the decades and continue to rope in through Office365 licencing, yet they can't afford to put in basic features...

 

I guess when you're too busy on moving things around in the UI you don't have time to actually implement "new" features..

1 best response

Accepted Solutions
best response confirmed by BrentM500 (Copper Contributor)
Solution
Excel desktop will get those features you are asking for in the near future. Currently, this only works in Excel on-line and on the Insider version of Excel.

View solution in original post