Data Validation dropdown lists – major improvement on the way

Before looking at the change itself in more detail, we'll include a quick overview of what Data Validation can do.

The Data Validation feature is intended to improve the accuracy of data input by allowing entries to be restricted to certain types of data and, for some of those data types, specified ranges of values. For example, it is possible to set the Data Validation for a cell or range of cells to a range of dates. Here, we have selected Data Validation from The Data Ribbon tab and then set the chosen Date from the Allow: dropdown and set the Data: dropdown to between, before setting the Start date: and End date: to cell references B1 and B2 respectively:

Having applied our Data Validation to cell B5, when we try to type a value into the cell, it will only be accepted if it meets the criteria we have set. If not, an error message will be displayed. The Error Alert tab allows us to enter our own error message rather than having to rely on the default, generic message:

It is worth noting the settings in the Style: dropdown. Given the term 'Style' it's easy to think that these three options just change the icon that will be displayed when the Error Alert is triggered, but the choice you make is more important than that. The Error Alert Style also controls the options available to the user when they enter a value that doesn't meet the criteria set. If you choose Warning or Information, the user will still have the opportunity to accept the value whereas the Stop Style only allows the user to Cancel their incorrect error or Retry to enter an acceptable value:

As useful as Data Validation can be for improving the accuracy of data entry, the feature also has another capability: it can be used to allow the user to select an entry from a list rather than having to type it in. This is easy enough to set up, you just need to choose List from the Allow: dropdown and set the Source: to a cell range. As well as a straightforward range using cell references, the Source can also be entered as a Range Name or a reference to a Dynamic Array. Here, we have set our list up as an Excel Table and allocated the Range Name: ProductList to the values in our only column. We have then used this as the Source of our List in our Data Validation setting:

The 'In-cell dropdown' option is enabled by default and, when the user clicks on the dropdown, they can then choose an item from the list. If they choose to type their value in instead, this will be checked against the list to see if it is allowed or triggers the Error Alert.

Although this is a much simpler way to create a dropdown list than using one of the Developer Form Controls, it did require the user to either type in the entry in full for comparison to the list, or to use the mouse to click on the dropdown and then select the required entry. It is also possible to select the entry using the keyboard: Alt+Down (or Up) Cursor will display the list and then the Up and Down keys can be used to select the required value and the Enter key then used to accept it.

This is where the recent/imminent enhancement comes in. Now, when you start typing in your entry, AutoComplete is implemented, and the list will be displayed and limited to display items that match what you have typed, based on the first characters. Given this change might not have yet been implemented in all versions of desktop Excel, here is the same workbook using Excel for the web in a browser, showing that entering 'Tr' displays those items in the list that start with Tr and allowing the highlighted item to be accepted by just pressing the Enter key:

Combined with the ability to use the SORT() and UNIQUE() Dynamic Array functions to create sorted lists to act as the Data Validation List Source, AutoComplete will make Data Validation dropdowns much easier to use. Hopefully, it won't be too long before it pops up (or drops down) in your version of Excel.

• Depending on Data Validation Lists