Excel Data Validation List

What is Excel Data Validation?

Excel Data Validation is an option in excel by which we can restrict the operator by entering the false entry in Excel sheet, it has some criteria for a user and from this criteria or conditions, the user can not enter the wrong input. We will understand this by the following images:-

Go to Data Tab –>

Image 01
In this image, we can see that in the Data Tab –> Data Validation and when we click on Data Validation following image will appear
Image 02
In this 2nd image, there are three table —> “Settings”, “Input Message”, “Error Alert” and then Validation criteria “Allow”:- Any Value
When we select an area in excel and apply this condition in data validation, this criterion accepts all the numbers or text, it does not have any restriction on the field. But when this feature will restrict us to put incorrect data, we will learn in further in this blog.
See image:-
image 03

This is all list of data validation:-

“Whole number”, “Decimal”, “List”, “Date”, “Time”, “Text length”, “Custom”.

In this tutorial, we will learn about all of the above options.

“Input Message ” is the second tab in the Data Validation box and the third one is “Error Alert”

Input Message box in Data Validation

Image 04
“Input Message” box display the message about the cell or area containing the validation about, like in the above image we can see that we  have a message like in “Title = => Serial No.” and in the “Input message” box “Only put serial no. 1 to 10” and we can see above in the excel coloured area, when we put our cursor into cell “C1” it displays the message as we put in the “Input message” box.

 Error Alert box in Data Validation

See the image below:-
Image 05
Now in this Data Validation “Error Alert” message box,  we notice that we have checked the checkbox “Show error alert after invalid data is entered” if we do not check this check box then the message will not be displayed.
Now come to “Style” and the option are “Stop”, “Warning” and ” Information” all the option are available for us that in which option we want to see the message from these three options.
Then “Title” is that what title we want to give that error message, we have put Wrong Input.
Then “Error Message” what message will pop up when someone will put wrong data in the validation field.
See the below image:-
Image 06
We put “11” in the colored field that field has validation and the message is showing that “Wrong Input” as the error title and the message is “Please enter the no. between 1 to 10 only” this colored area has validation to put data only from 1 to 10, not beyond this.

Criteria Examples of Data Validation

Any Value Data Validation

If we select this option in data validation that means we can put any data into the data validation field whether a numeric or a text one.

Whole Number Data Validation

Now come to second option Whole number, if we choose this option that means we can specify the maximum and minimum range in the validation area, see the image below:-
Image 07
We have set the limit between minimum “1” and maximum “10” the data validation field will accept the data within this range only not apart from that.

Decimal Data Validation

In this image we can see that we have set the data validation value from 100 to 125 and this has an additional feature that it can accept the decimal figure also, like in the below image we can see “100.253” and “124.362” but not greater than “125”. It accepts the value in decimal also.
Image 08

List Data Validation

Now move forward to another option and it is “List” option in data validation. See the image below
Image 09
In this image, we can see that in cell “A1 to A3” we have put some name and now the cursor position in cell “B1”. In the “List” option in data validation, select “list” from allow field and type all the names in the “Source” field or drag the name range from “A1:A3”, result would be same from both option and press “OK” then go to cell “B1” the following image will appear.
The drop-down menu has been created by simply pressing “OK” button.
Image10
In the above image, we can see that when we click on cell “B1” then a drop-down menu list open with containing all the list details of from “A1 to A3”.

Date Data Validation

In the below image the selected area has a data validation for the “date range”. From start date contain the date range of cell “A1” and end date range contains the date of cell “B1”.
Image 11

If any other date will be entered in the selected validated area the date will not be accepted by excel.

Time Data Validation

In the below image we have put the time range in start time and end time and the selected area will not accept any time range apart from this time range.
Image 12

Text Length Data Validation

Text length validation option gives a condition that we can put the text into a validation area, that we can only put the data according to the condition as shown in the below image.
Image 13

And if we try to write something beyond our validation condition  then it does not allow us to do that by showing below error message,

Image 14

Custom Data Validation

Custom data validation is the validation when we put our own condition in the formula point and get the result as appear in the below image:-
Image 15

And if we enter the value beyond criteria then it shows the below message:-

Image 16

In this Custom Data Validation option, we can create more formula according to our need.

Circle Invalid Data

If we have a data and this data range contain a data validation of any given range as in the following image:-
Image 17

In the above image we can see that we have given a range from 100 to 150, so the data range will accept the data from this criteria only but if there is any data beyond this range it will circle the cell, which is containing the wrong value.

How to do that, just see below instruction and image:-
Excel Data – ->Data Validation –> Circle Invalid Data
Image 18

and once you click on the “Circle Invalid Data” then the following result will come out from the selected data range which is containing the data validation.

Image 19

In this image, we can see that which figure is beyond our data validation limit i.e. 100 to 150, the “Circle Invalid Data” option put a circle around that figure and again if we want to remove the circle then we have to follow this command.

Go to Data — —-  Data Validation —- Clear Validation Circles and click on it.

Image 20
Once you click on the button “Circle Validation Circles” we will get the following image:-
Image 21
All the red circles have been removed.

How to Remove Data Validation

Now we have learned all the option in data validation and now we will learn about how to remove data validation. Now simply follow the step
Click Data —- Data Validation —- Click on Data Validation button and then click “Clear All” button, appearing below in the given image and then “OK”
Image 22

All the data validation have been removed from the selected area and we can work on Excel as normally.

There is one more way to remove data validation from the given range. Simply go to a blank cell which does not contain any validation and copy it the go to the validation area and select all the validated area and right click as mentioned in the below image and click on paste special.
Image 23

Once you click on paste special you will get the following image:-

Click on the “Validation” radio button and simply press “OK” and we are done.

This is a very easy way to remove data validation from the easy step.
Thanks
 Narendra(www.excelnsg.com)

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.