How to enable auto-complete again for validation of data in dropdown list?

Question

About two weeks ago Excel365 enabled auto-completion for drop-down list data validation. So when I started to fill in a piece of data it would pull from the list a list of options that met the criteria and allow me to select the options greatly reducing error and speeding up the fill. This is a basic change that I have waited decades for MSFT to implement.

However, now Excel is no longer pulling the pick list and the filling text. So it returned the old way and to get a correct completion is now required to type the correct and full text or selecting the appropriate option with the mouse. As I use lists with thousands of options, the process is very slow and error prone.

What happened? How do I get this auto-completion option in drop-down validation menus working again?

Answer 1

Hello,

Welcome to the Microsoft 365 and Office Community.

I hope this finds you well. Please follow the steps below to resolve the issue:

1. Verify if the data validation drop-down list is correctly configured. You can do this by selecting the cell or range of cells where the drop-down list is configured, then go to the "Data" tab on the ribbon and click on "Data Validation" in the "Data Tools" group. Make sure the list of options is correctly defined in the "Settings" tab.

2. Check the settings for auto-fill. To do this, go to the "File" tab on the ribbon and click on "Options". Then, click on "Advanced" in the options window and scroll down to the "Editing" section. Make sure the option "Enable AutoComplete for cell values" is checked.

3. Verify if auto-fill is enabled in Excel in general. To do this, go to the "File" tab on the ribbon and click on "Options". Then, click on "Advanced" in the options window and scroll down to the "Editing" section. Make sure the option "Enable AutoComplete for cell values" is checked.

Restart Excel after making changes to the settings to ensure that the changes take effect.

Responding to users reading this thread, support help didn't resolve the issue.

I hope Microsoft takes action soon.

Answer 2

Hello,

Welcome to the Microsoft 365 and Office Community.

I hope this finds you well. Please follow the steps below to resolve the issue:

1. Verify if the data validation drop-down list is correctly configured. You can do this by selecting the cell or range of cells where the drop-down list is configured, then go to the "Data" tab on the ribbon and click on "Data Validation" in the "Data Tools" group. Make sure the list of options is correctly defined in the "Settings" tab.

2. Check the settings for auto-fill. To do this, go to the "File" tab on the ribbon and click on "Options". Then, click on "Advanced" in the options window and scroll down to the "Editing" section. Make sure the option "Enable AutoComplete for cell values" is checked.

3. Verify if auto-fill is enabled in Excel in general. To do this, go to the "File" tab on the ribbon and click on "Options". Then, click on "Advanced" in the options window and scroll down to the "Editing" section. Make sure the option "Enable AutoComplete for cell values" is checked.

Restart Excel after making changes to the settings to ensure that the changes take effect.

I also use dropdown lists in excel every day. And I also noticed that on 04/18/2023 Microsoft removed this previously enabled function.

Very frustrating and long awaited function has been removed from excel functions.

I really hope that Microsoft releases the update to fix this problem for good.

Answer 3

We appreciate the feedback.

The problem is being analyzed by the Microsoft engineering team and we will be back shortly with a solution.

Answer 4

Hello,

Welcome to the Microsoft 365 and Office Community.

I hope this finds you well. Please follow the steps below to resolve the issue:

1. Verify if the data validation drop-down list is correctly configured. You can do this by selecting the cell or range of cells where the drop-down list is configured, then go to the "Data" tab on the ribbon and click on "Data Validation" in the "Data Tools" group. Make sure the list of options is correctly defined in the "Settings" tab.

2. Check the settings for auto-fill. To do this, go to the "File" tab on the ribbon and click on "Options". Then, click on "Advanced" in the options window and scroll down to the "Editing" section. Make sure the option "Enable AutoComplete for cell values" is checked.

3. Verify if auto-fill is enabled in Excel in general. To do this, go to the "File" tab on the ribbon and click on "Options". Then, click on "Advanced" in the options window and scroll down to the "Editing" section. Make sure the option "Enable AutoComplete for cell values" is checked.

Restart Excel after making changes to the settings to ensure that the changes take effect.