Data Validation Tool

If you are using Excel to input data, you can use its data validation tool to ensure that the information being input is as accurate as possible.  For example:

  • Allow only a number to be input
  • Allow only a particular range to be input e.g. A-R
  • Display a message when the cell is selected
  • Tailor error messages for particular errors
  • Automatically detect errors that don’t meet the rules (useful if using pre-populated data)

You can find the Data validation tool on the Data tab, in the Data Tools section:

Data validation tool

It’s worth spending some time looking at the different parts of this tool.  Essentially there are three things you can set it to do: check the input from a user, display a message when a user clicks in the cell, and display an appropriate error message when invalid data is entered.  Once set up, you can apply this to any cell you want, and you can use the drop down on this same button to circle any invalid data.

Circling invalid data

In this example, I entered some data, and then applied a data validation rule that said it needed to be a whole number between 1 and 100.  I then switched on the circling of invalid data.

As you can see, this circles the values above 100. This can be a really useful tool, not only for gathering input from users, but also validating data you already have.

Leave a Reply

Your email address will not be published. Required fields are marked *