We are going to cover Data validation techniques in Excel. Data validation is a very important feature of Excel document. This feature helps to prevent user from entering invalid data on the cell. Various types of data validation can be done in excel. How these validations can be applied in excel document is shown in this tutorial using an example. Please note that the data validation feature of Excel can be expanded to fit a vast majority of applications. Gaining a solid knowledge will greatly improve your Excel workflow.

Suppose you have to enter data in product table with the following fields and restrictions.

Item ID : Numeric and start from 1
Name : Text and length must be between 10 and 50 characters
Price : Decimal and must be between 10 and 1000
Manufacturing Date : Date and must be greater than or equal to specific date
Expire Date : Date and must be greater than manufacturing date

Lets get started learning Data Validation Techniques:

Data Validation

Select cell under Item ID and click on Data Validation toolbar button. Click on Data validation option to open Data Validation window.

Data Validation

Uncheck Ignore blank option, select Whole number and greater than option.

Data Validation

Data restriction for Item ID is number and greater than 0. So select 0 as minimum value to prevent user from entering 0 in this field. 

Data Validation

When the user will set 0 in this cell then the following error message will appear.

Data Validation

To set restriction for Name field add the following validation rule.

Data Validation

To set custom helping message for the user, use the following option of Data Validation window. 

Data Validation

When the user will select the cell then the following message will appear.

character limit

To add Error alert message for invalid data use the following option of Data Validation window.

Error alert

If the user will enter a product name less than 10 or greater than 50 then the following message will appear.

Excel tutorials

Add the validation for Price fields like below to set the restriction.

Data Validation

When the user will add Price value less than 10 or greater than 1000 then the following error message will appear.

Data Validation

Add the following rule for Manufacturing Date.

Data Validation

Here 7/7/2017 is set. So the user can add this date or any date more than this value.

Excel Tutorial Data

The following error will appear if the user enters any date below 7/7/2017.

Data Validation

Add the following rule for Expire Date to restrict user from adding any date less than or equal to manufacturing date using custom rule. Add the formula, =f2>e2

Data Validation

The following error will appear if the user enters invalid date.

Excel Tutorial Image

Now your excel document is fully validated according to the above restrictions. If you have more questions regarding Data validation techniques in Excel, please let us know.

Leave a Reply

avatar
  Subscribe  
Notify of