Did you know the Data Validation List is now searchable?

Dec 21, 2023 | Excel Hints and Tips

I talk a lot on my training courses about the importance of data being consistent and accurate.  Most of the issues that occur when analysing data are caused by this.  This can be caused by a lot of reasons.  There could be different people entering the data in their own preferred way or laziness meaning that over time certain words are abbreviated for speed or anyone can just mistype a word.

Anything that can help this is going to be beneficial and minimise (but not prevent) the rubbish in then rubbish out scenario.  Data Validation in Microsoft Excel has been able to produce drop down lists for people to choose from for many years but it is only recently that the option of making this searchable has been available.  This is a game changer for long lists which may have taken ages to scroll through to get to the right entry.

Creating a drop-down list using Data Validation

  1. On a separate sheet create the list of text to appear in the drop-down list.  Creating this on a separate sheet just keeps it out of the way from the actual data.  You may want to sort this alphabetically, but this is not vital.
  2. Select the cells where the drop-down list will appear.
  3. On the Data tab click on the Data Validation icon.
Data Validation icon image

4. By default every cell can accept any value (numbers, text, dates etc).  Change Any value to List.

5. In the Source box select the range of cells where the list of items to appear in the drop-down list was created.

Data validation settings image

6. The Input Message tab is optional but can be used to contain a Title and Input message that appears when the cell containing the data validation is clicked on.  This can provide clarity if there is any ambiguity about what needs to be added.

7. The Error Alert tab is more important to consider.  By default the Style is set to Stop.  This means that any attempt to add data that is not in the list created will be prevented.  The other 2 options are Warning and Information.  Warning will warn individuals that the data being entered may be incorrect but will still let that be used.  Information will tell the data inputter that the data might be incorrect but immediately accepts it anyway.  Warning and Information are useful if you think there may be other options that have not been considered and therefore omitted from the list but the only option to prevent the entry of data that is not in the list produced is Stop.

8. For any of these options a Title and Error message can be added to help people understand why their data may be incorrect.

Data validation Error Alert image

Entering Data using the drop-down list produced

  1. When you click in one of the cells where the drop-down list has been created an arrow appears to the right of the cell.
  2. When you click on the arrow a list of the items created in the list appears.  This may be scrollable if the list is long.
  3. If you start typing in the first letter (or couple of letters) of the entry the list will shorten to only show those that begin with these letters.  This is the new feature!!

Full List

Full list image
Searched list image

Searched list

Further Reading

If you’ve enjoyed reading about how the Data Validation List is now searchable, there are some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.