AutoComplete for Dropdown Data Validation Lists in Excel for Windows

Data Validation may be used to control what end users may input into a cell. This may be accessed by going to the Data tab of the Ribbon, then go to the Data Tools group and click the Data Validation icon (ALT + A + V + V or ALT + D + L), viz.

The default setting for all cells in Excel is to allow any value (pictured). This can be changed by changing the selection in the ‘Allow’ drop down box. It may be modified to any of the following:

Most of these criteria do exactly what they say on the tin: e.g. by choosing ‘Decimal’, the input must be a number, whereas ‘Whole Number’ allows for integers only. However, making a selection from the ‘Allow’ drop down box is only the first part of the data validation process.

Here, let’s focus on list:

Here, typing in

North,South,East,West

will provide an in-cell dropdown box as follows:

A range may be used instead. We could select a range instead, e.g.

Some time ago, we showed how to create a data validation list that will “narrow down” the list as you type in the cell using dynamic arrays. But now, that’s no longer needed:

Microsoft has announced that they are speeding up data entry and validation with AutoComplete for dropdown lists, which is now available for some – alas, not all – users in Excel for Windows in the Beta Channel.

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

This helps speed-up discovery of the right Data Validation values, and in turn, reduces time spent scrolling through the dropdown list, dealing with Data Validation errors, or writing complex code to enable it.  Furthermore, Microsoft has added that they plan to add excluding duplicates from the dropdown list before it is rolled out into general production.

AutoComplete for Data Validation drop-down list is available in Beta Channel with version 2112, build 16.0.14818.10000 or later.  Not everyone will get it immediately though, as Microsoft monitors usage and bugs.  But be patient – it’s a very cool feature!