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.
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.
The uses for all seven options are explained in the below image.
|Options||Data Validation Type|
|Whole number||Only Whole no. can be entered|
|Decimal||Only decimal (percentage) values can be entered.|
|List||Entries are restricted to a list of items only.|
|Date||Entries are restrited to a range of dates only.|
|Time||No entry of time data outsite the defined frame.|
|Text length||Limit entries to a certain number of characters.|
|Custom values||Limit entries to unique values only (no repeated entries)|
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
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,
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.
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.
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.
Date option used to provide only date options. All other options will remain same like decimal and whole number.
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.
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.
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.
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.
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: .
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
Also see the video which explains this topic in elaborative way.