PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

HOW TO USE DATA VALIDATION IN EXCEL?

WHAT IS DATA VALIDATION IN EXCEL?

Excel is a great tool which provides countless useful tools for data analysis. In addition to this, it has great tools for making wonderful reports , charts etc. Out of these features, one of the features is DATA VALIDATION which helps us control the data entered in the cell and provide useful information for our users.

Data validation means to check the data before entry. It gives us the power to condition the values which can be entered and rejected into any cell.

BUTTON LOCATION FOR DATA VALIDATION IN EXCEL

The data validation option can be found under the DATA TAB in DATA TOOLS SECTION as shown in the picture below.

data validation in excel
BUTTON LOCATION FOR DATA VALIDATION

EXAMPLE: USE DATA VALIDATION IN EXCEL

THE STEPS TO VALIDATE THE DATA HAS BEEN DEMONSTRATED USING AN EXAMPLE.

SCENARIO:

Let us create a scenario where we need to enter the age and the age should be more than 18 and less than 30.

A cell containing the text “enter your age” is there. Next cell is the one to accept the input from the user.

STEPS:

  • Click the cell where input is to be taken .
  • Click CONDITIONAL FORMATTING button.Following dialog box will open.
data validation in excel
PUT THE CONDITION AS PER REQUIREMENT

PUTTING THE CONDITION IN PLACE:

The dialog box contains many conditions. We can choose the one we want.

For our example,

  • we’ll choose ALLOW: WHOLE NUMBER. [As we want the age as whole numbers only ].
  • DATA: BETWEEN and put the values as 18 for minimum and 30 for maximum as per the example statement.

ENTERING THE INPUT MESSAGE:

We have put the condition. But how would we tell our user about the condition. Isn’t it the best idea to be able to show a comment when a particular cell is selected.

So, Excel provides us this option which is very practical and helpful.

STEPS TO PUT THE INPUT MESSAGE IN DATA VALIDATION:

  • Select the INPUT MESSAGE TAB in the DATA VALIDATION DIALOG BOX as shown in the picture below.
  • Enter the title as per your requirement. We have entered AGE for our example.
  • Enter the INPUT MESSAGE, the message which you want to show when the validated cell is selected by the user. For our example , we have put the message as KINDLY ENTER YOUR AGE BETWEEN 18 AND 30.

 

DATA VALIDATION EXCEL
PUT THE CONDITION AND INPUT MESSAGE AS PER REQUIREMENT

ENTERING THE ERROR ALERT MESSAGE:

In addition to the INPUT MESSAGE, we can put ERROR ALERT also.

STEPS TO ENTER ERROR ALERT:

  • Choose the ERROR ALERT TAB in the DATA VALIDATION DIALOG BOX.
  • Check the SHOW ERROR ALERT AFTER INVALID DATA IS ENTERED.
  • Choose the Style from INFORMATION, STOP OR WARNING.

  •  Enter the title. For our example we have entered INVALID AGE.
  • Enter the ERROR MESSAGE. For our example we have entered, KINDLY ENTER AGE BETWEEN 18 AND 30.
After all the settings are done, click OK.
data validation in excel
SET THE ERROR ALERT.Click OK.

RUNNING THE EXAMPLE

After all the settings, we are ready to check our data validation.

For testing purpose, we put 31 and the cell didn’t accept the value and showed the error as we set

VALIDATE ALREADY FILLED DATA USING DATA VALIDATION

DATA VALIDATION provides us with a very useful functionality using which we can validate the data which is already present.

We have two options for validating already filled data.

  1.  CIRCLE INVALID DATA
  2.  CLEAR VALIDATION CIRCLES

Let us try these options using some random data.

EXAMPLE:

Let us take the rainfall data of a city for twenty week
Suppose we want to highlight the data when the rainfall was between 5 and 10.
The data is shown below.
 
RAINFALL DATA

STEPS TO HIGHLIGHT INVALID DATA

STEPS TO HIGHLIGHT INVALID DATA :
  • Select the cells containing the rainfall data.
    Go to DATA TAB> DATA VALIDATION DROP DOWN > DATA VALIDATION.
    It’ll take us to the standard data validation dialog box.
    Choose the options as shown in the animated picture below.
    Make the selections as 
  • ALLOW: WHOLE  NUMBER
  • DATA: BETWEEN
    MINIMUM : 5
    MAXIMUM:10 [ minimum and maximum fields will appear as soon as we choose DATA as BETWEEN ].
    Now , again go to DATA TAB> DATA VALIDATION DROP DOWN > CIRCLE INVALID DATA.
    As soon as we make the choice, all the invalid data will be encircled.
    Look at the picture below for the reference. 
STEPS TO HIGHLIGHT THE INVALID DATA USING DATA VALIDATION

The final picture showing the encircled data.

 

HIGHLIGHTED INVALID DATA WHICH DOESN'T SATISFY THE CONDITION

REMOVING DATA VALIDATION

If we have circled the invalid data, we can easily clear these circles using the CLEAR VALIDATION CIRCLES under the DATA VALIDATION DROP DOWN LIST in DATA TAB.