Excel Data Validation

Excel Data Validation is a option in excel by which we  can restrict the operater by typing the false entry in the Excel sheet, it has some criteria for user and from this criteria or condition the user can not enter the wrong input.

We will understand this by following example:-

Go to Data Tab -->

Image 01

In this 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 a area in excel and apply this condition in data validation, this criteria accepts all the numbers or text, it does not have any restriction to the field. But when this feature will  restrict us to put incorrect data, we will learn in further in this  blog.

Now come to the second image :-

image 03

This is the all list of data validation :-

"Whole number", "Decimal", "List", "Date", "Time", "Text length", "Custom".

We  will discuss all of the above given option one by one.

"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 same 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 check box "Show error alert after invalid data is entered" if we do not check this check box then the message will not be displays.

Now come to "Style" and the option are "Stop", "Warning" and " Information" all the option are available for we that in which option we want to see the message from these three option.

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 coloured field that field has validation and the message is showing that "Wrong Input" as the error title and message is "Please enter the no. between 1 to 10 only" this coloured 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 with-in 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 a 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 accept 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 have been created by simple 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 shows 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's according to our need. 

Circle Invalid Data

If we have a data range 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 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 clikc on  paste special.

Image 23

Once you clik 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 very easy way to remove data validation from easy step.

Thanks and Regards

NarendrasExcelTips.blogspot.com