How to use data validation lists to produce consistent data

Oct 7, 2021 | Excel Hints and Tips

I often talk about the rubbish in / rubbish out scenario.  If you don’t have consistent data then you cannot analyse it properly.  Using lists in data validation in Microsoft Excel can really help with this. 

Creating a data validation list 

There are 2 steps to working with validation lists. Firstly, producing a column containing the options you need to appear in the list. Secondly, using Data Validation to apply this list to the required cells in the spreadsheet. 

Creating the items that will be in the list 

It is good practice to set up the lists that people are going to be picking the items from on a separate sheet.  This is tucked away from the rest of the data and accidental changes to it are much less likely.  It can even be hidden if you prefer. 

  1. Create a new sheet by clicking on the ‘+’ next to the sheets at the bottom 
  2. Type in the items you want to appear in the list in a column 
  3. These must be in the order you want them to be viewed in the drop down.  If this is alphabetical it can always be sorted 
List of data

Setting up the data validation 

Now we can set a range of cells to allow options to be picked from this list 

  1. Select the cells that are due to be validated from the drop down we are going to produce 
  2. If you are unsure how long this list will be it often helps to select more cells than you expect so you don’t need to change anything if the list grows 
  3. From the Data tab select Data Validation 
  4. Allow data from a List 
  5. For the source select the cells that contain the options you want in the drop-down list that you created earlier.  This is the one that is often on another sheet.  It often helps to select a few extra cells in case there is anything you have forgotten to include or if the list grows in the future.  Although the extra blank cells are selected there will not be gaps in the drop down list as long as the tick box Ignore Blank is checked 
List data validation

6. Input messages that show when you click on the cell and error alerts can be changed if required 

7. By default a generic error message will appear if an incorrect entry is typed. And text that does not appear in the list will not be accepted. 

8. Click on OK 

Hiding the sheet containing the list items (optional) 

Hiding this sheet is not vital but if others are using the spreadsheet it makes it tidier for them to work with and gives them less opportunity to delete the sheet without realising the implications of their actions. 

  1. Right click on the sheet 
  2. Select Hide 
Hide sheet

If the sheet needs to be unhidden to make changes follow the same steps but Unhide. 

Consider using list data validation whenever data is being entered that comes from a predefined list.  If the list is fixed to some degree but can have adhoc answers occasionally then the error alert style can be changed from Stop to Warning.  This will warn the user if they type in something that isn’t in the list provided but they can still proceed with the entry once it has been carefully checked.  

Further Reading

If you’ve enjoyed reading about how to use validation lists in Microsoft Excel, there’s some other blogs below that you might find useful:

Want to learn more about Excel? Then email to discuss how I can help or have a look at the Excel Courses I run.