Awesome Examples of the Data Validation Use in Excel

What is Data Validation?

Data validation is a feature in Excel that helps to control what a user can enter into a cell.

What are the Data Validation options?

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.

The settings tab also includes two check-boxes:

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.

Data Validation Criteria example

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.

Input Message and Error Alert customization

You can create an “Input Message” for the data validation cell.

1. Use Excel shortcut Alt A V V to open the “Data Validation” window.
2. Ctrl + Page Down will bring you from the “Settings” tab to the “Input Message” tab in the window.
3. Input the title and the message you’d like to use for the error message.

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.

What are Error alert styles?

1. Stop – Stops users from entering invalid data in a cell. Users can retry but must enter a value that passes data validation. The Stop alert window has two options: Retry and Cancel.
2. Warning – Warns users that data is invalid. The warning does nothing to stop invalid data. The Warning alert window has three options: Yes (to accept invalid data), No (to edit invalid data), and Cancel (to remove the invalid data).
3. Information – Informs users that data is invalid. This message does nothing to stop invalid data. The Information alert window has 2 options: OK to accept invalid data, and Cancel to remove it.

Here how it would look like:

Data Validation List

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:

1. Use Excel shortcut Alt A V V to open the “Data Validation” window.
2. Choose “List” in the validation criteria.
3. Put the cells with the values of the desired list. In our example, it’s cells C2: C3.

Now you can choose the value from the list in the cell as shown on the picture below.

Clear Data Validation rules

To clear the assigned data validation rules, use the following logic:

1. Put your cursor in the cell with the assigned data validation you would like to delete.
2. Use Excel shortcut Alt A V V to open the “Data Validation” window.
3. You can push “Clear All” mouse-free with the shortcut Alt + C (as we underlined C before).

Excel cleared the Data Validation rules, great job!

Conclusion

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.

‍