data validation excel

Data Validation in Excel: How to use it,8 awesome uses of excel data validation

Data Validation in Excel

Data validation in excel is a very useful tool. Its main functionality is to create constraints while data entry in excel. For example, we could put a constraint that no duplicate value should enter in a cell or column or the value entered in a cell should be between 5 to 25. It also reflects a message about the condition required to enter the data in that cell, when we put a wrong entry.

How to add data validation in excel sheet

To apply data validation in excel first we will go to Data >> Data Validation >> Data Validation. After that we will click on Data Validation then we will get an input box as illustrated in the below image.

Data Validation in Excel
Data Validation in Excel

This box have three tabs, Settings, Input Message and Error Alert. In the setting tab we have validation criteria , we can use Allow drop down to choose validation rules. There are 8 different options there, the first one is Any value which allows all value to enter but we can use input message and error alert in combination to show some alert information. All eight options are mentioned below.

how to apply data validation in Microsoft excel
Setting option in excel

The uses for all seven options are explained in the below image.

OptionsData Validation Type
Whole numberOnly Whole no. can be entered
DecimalOnly decimal (percentage) values can be entered.
ListEntries are restricted to a list of items only.
DateEntries are restrited to a range of dates only.
TimeNo entry of time data outsite the defined frame.
Text lengthLimit entries to a certain number of characters.
Custom valuesLimit entries to unique values only (no repeated entries)
description of options

Now we will be discussion different options of data validation list one by one.

Whole number can be used to restrict entry for all data types except the whole numbers. There are different options that we can use like in between, not between, equal to, not equal to, greater than, less than, greater than, or equal to, and less than and equal to. You can see these options in the first box of the below image

Input message excel
Input message excel

In the above example, we selected the greater than option and put value 18 in the minimum input box. By doing so we are assuring that no one can enter any number smaller than 18, if any tried to do so it will give us an error prompt as illustrated in the below image. We will learn about how to create a customized error alert in excel in the latter part of this article,

data validation error
how to create a customized error alert in excel

Decimal would be used when we need to restrict entry of only decimal numbers. Decimal will have the same options in Data as the whole number were. To explain this we will see an example below where we selected between option and we got minimum and maximum as input to provide. Here it should be remember that we should use maximum 1 as input value.

how to create a validation rule in excel
decimal input option

List option is highly used among all allowed option. The strength of this option is that it gives us to select a list of entries and then only these entries can be used in the cells.

excel data validation list from table
excel data validation list from table

In the above example, we have a list of entries in rows A4 to A9. We will use this in the Source option of the box. after selecting this we will see only these options in the C4 cell drop down list unique values.

excel data validation drop down list unique values
excel data validation drop down list unique values

Date option used to provide only date options. All other options will remain same like decimal and whole number.

how to make a drop down of dates excel
how to make a drop down of dates excel

In the above example we used dates input in Start date and End date. Any option, other than that would not be accepted in the cells.

Time option also works same as Date.

varify multiple address in excel file
Time option in drop down list

Text Length helps data validation in excel when we wanted to put constraint for text length entry. All other options will remain same as the other options have.

apply warning text excel
text option in data validation in excel

In the above example, we selected greater than option and we put the minimum option as 12 which means if we put a text less than 12 then we will get warning text.

Custom is the last option in this series which gives us freedom to use our own options. We use formula to create constraint in this option.

excel data validation custom list
Custom excel data validation

In the above example we use validation for text entry only. If we use any input other than text then it will show us error.

Now we will discuss about second tab Input Message. We are using input message in excel data validation to show a message that what kind of entry are permissible. Input Message have two boxes first is Title and second is Input Message: . After filling these, it will be reflecting in the input cell.

input message excel
input message in excel

Finally the last option is Error Alert which shows up when we are providing wrong input. To use this we need to use Style which has three options Stop (will not allow entry), warning(gives warning and option to continue or cancel) and Information (gives a prompt only). The other two options are Title and Input Message: .

how to create a customized error alert in excel
how to create a customized error alert in excel

That is all for this article. We will meet again with some new interesting article.

You also can read few articles which helps in further understanding of validation rule excel

https://www.howtoexcel.org/tips-and-tricks/11-awesome-examples-of-data-validation/

https://exceljet.net/excel-data-validation-guide

Also see the video which explains this topic in elaborative way.

excel data validation youtube

Leave a Comment

Scroll to Top