Data validation is a feature in Excel that helps to control what a user can enter into a cell.
When we create a data validation rule, there are eight available options to validate user input:
Any Value – the user does not perform any validation. Note: For when we previously applied the data validation with a set Input Message. Even when we select Any Value, the message will still display when the cell is selected.
Whole Number – only whole numbers are allowed. Once we select the whole number option, other options become available to further limit input. For example, you can require a whole number between 1 and 10.
Decimal – works like the whole number option, but allows decimal values. For example, with the Decimal option configured to allow values between 0 and 3, values like .5, 2.5, and 3.1 are all allowed.
List – only values from a predefined list are allowed. The values are presented to the user as a dropdown menu control. Allowed values can be hardcoded directly into the Settings tab, or specified as a range on the worksheet.
Date – only dates are allowed. For example, you can require a date between January 1, 2018, and December 31, 2021, or a date after June 1, 2018.
Time – only times are allowed. For example, you can require time between 9:00 AM and 5:00 PM, or only allow times after 12:00 PM.
Text length – validates input based on a number of characters or digits. For example, you could require a code that contains 5 digits.
Custom – validates user input using a custom formula. In other words, you can write your own formula to validate input. Custom formulas greatly extend the options for data validation. For example, you could use a formula to ensure value is uppercase, a value contains “XYZ”, or a date is a weekday in the next 45 days.
Ignore blank – tells Excel to not validate cells that contain no value. In practice, this setting seems to affect only the command “circle invalid data”. When enabled, blank cells are not circled even if they fail validation.
Apply these changes to other cells with the same settings – this setting will update validation applied to other cells when it matches the (original) validation of the cell(s) being edited.
Let’s say we want to assign “ID” to Brian and it can only be a whole number over 100.
First of all, you need to be in the relative cell to which we assign the data validation rules, in our case it’s B4. Then, use Excel shortcut Alt A V V to open the “Data Validation” window.
As a validation criteria, let’s pick “Whole number”.
For “Data” choose “greater than” option.
For our purpose, the validation criteria is the whole number greater than 100. Click “Ok” to apply the assigned rules to the cell.
Now let’s try to input the number less than 100 to the cell with the data validation assigned by us (cell B4).
Notice, that if we try to input 1 (less than 100), MS Excel gives us an error message.
You can create an “Input Message” for the data validation cell.
The message would guide you what kind of input you should use in the cell.
You can customize the error message in the data validation window.
Here how it would look like:
You can also create a Data Validation List as you can see in the picture below.
To create a data validation list, use the following logic:
Now you can choose the value from the list in the cell as shown on the picture below.
To clear the assigned data validation rules, use the following logic:
Excel cleared the Data Validation rules, great job!
Data validation is a good way to let users know what is allowed or expected.
It’s important to understand that data validation can be easily defeated. If a user copies data from a cell without validation to a cell with data validation, we simply destroy (or replace) the validation.
Learn more Excel functions and speed up your Excel workflow and Financial Modeling skills by playing our educational games keySkillset.
Start learning new skills with the help of KeySkillset courses and our learning management system today!