Data validation in Excel allows us to set parameters on what can be entered into a cell and we can also choose what happens when invalid data is entered. This is very useful in keeping the data tidy. We have more predictable data which can help with future analysis and calculation. As a quick example, if we want to collect data on how many pets each person has, we may end up having data such as “one”, “2”, “2 dogs”, “none”. With data validation, we can make sure only whole numbers can be entered.
How To Set Validation Criteria On Cell(s)
To set up data validation:
- Highlight the cells (or range of cells) we want to apply data validation on
- Go to Data tab and select Data Validation:
- By default of course, data validation would allow “any value” to be entered. Here in the “Allow” field, we can change that. Have a look at the list below
Whole number & Decimal
With whole number & decimal, we can make sure only numbers are entered into the cell. Of course choose wisely whether whole number or decimal should be selected. With example given at the very top, if we want to count the number of pets each person does, having decimals would not make much sense so whole number would be preferred.
Once whole number or decimal is selected, we will need to set further parameters around what numbers can be entered.
Again this can be particularly useful as we can further narrow down what can be entered. As an example if we are collecting data on how many days people are exercising in a month, it will be wise to set the parameter between 0 to 31 as you can’t have negative days or more than 31 days in a month.
If we want to allow a wide range of numbers to be entered, we can always set whole number/decimal between -100000000000 to 100000000000.
Date & Time
Date and time are similar to whole number and decimal. Once we selected either one, we will need to set further parameters to narrow down what can be entered. For example we can set validation criteria so that only time can be entered and must be greater than 6pm
In Excel, 1 is represented as 1 day (24 hours). So 6pm (18:00) as a number is 3/4 (18/24) as a number. For more information, you can read more here: Date/Time in Excel. What it means here is if we set “greater than” 18:00 as validation criteria, Excel will allow us to enter any value greater than 0.75 even including 421 or 129 or 492 although these numbers are clearly not a time.
To solve this, instead of using “greater than”, use “between” and set end time as 23:59. Doing this you will be able to prevent users from entering numbers such as 1 or greater. Fractions can still be entered as long as when converted back into time, they fall within the validation criteria.
This allows us to add a dropdown list. You can find more information here: Dropdown List in Excel. On that page, we go through how to add a dropdown list using data validation. And also how to add a dropdown list where the list changes depending on the value in another cell. For example:
What To Do When Invalid Data Is Entered
We can also choose what happens when invalid data is entered. Go to the Error Alert tab after clicking on Data Validation:
By default, Excel will always stop invalid data from being entered. An error message will pop up and the invalid data will be removed from the cell. In the Title and Error message fields, we can customize the message
Warning and Information are rather similar. Once invalid data is entered, an error message will still pop up. However we would have the option to allow or remove the data.
If we click “OK”, we are acknowledging the invalid data and allowing it to be entered. But if we click Cancel, the data will be removed.