ICAEW.com works better with JavaScript enabled.

Data Validation dropdown lists – major improvement on the way

Author: Simon Hurst

Published: 21 Feb 2022

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.
A significant enhancement in the way that Data Validation works with lists is on the way. It has already arrived in Excel for the web and should also soon appear in the desktop version. In fact, within the last few days it has appeared in a recent update to the Insider, Beta Channel.

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:

screenshot of an excel spreadsheet

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:

screenshot of an excel spreadsheet

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:

screenshot of an excel spreadsheet

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:

screenshot of an excel spreadsheet

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:

screenshot of an excel spreadsheet

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.

Join the Excel Community

Do you use Excel in your organisation? Are you using it to its maximum potential? Develop your skills and minimise spreadsheet risk with our Excel resources. Membership is open to everyone - non ICAEW members are also welcome to join.

Excel polaroid
Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.