Data Validation Excel

Data validation Excel is a feature that allows you to set the rules for the user as to what kind of data can be entered in the cell. You can also show the costume message for the user if he entered the wrong data.

What is Data Validation?

Data validation is a method of checking and confirming the validation of data before using it. Data validation is important for any task that involves working with data like collecting, process, and representing data. It is important to avoid errors and improve the outcomes.

In Excel, it limits the user to enter a certain kind of data in the Excel worksheet.

Data Validation Excel with example

In this example, we will learn about who limits the user to enter certain data.

Here are some features that Excel data validation data can perform:

Here are some features that Excel data validation data can perform:

  • It takes only text and numerical values in the cell.
  • It allows data only on a specific length.
  • It gives the option to restrict the time and date in the given range of values.
  • It shows the input message on selecting the cell.
  • It also finds incorrect data in the validated cell.
  • When the incorrect value is entered the warning message window pops up.

For example, a question is asked by you about how much glass of water you drink in the day. And the answer range is between the number 1 to 20.


How to create the data validation rules

Follow these steps to create the data validation rule.

Click cell B2 to activate the cell.

In the ribbon on the data tab in the tool group press the Data Validation option.

data validation in Excel

A Data Validation dialog box pops up.

Settings Tab

Then in the setting tab, in the Allow list choose the whole number.

In the next Data list, choose the option between.

Enter the minimum and the maximum values.

Setting Tab in Data Validation

Input Message Tab

Now on the Input message tab when click on the cell.

Enter the Title which is Glasses of Water.

Then write the message in the input message.

Input Message Tab in Data Validation

Error Alert Tab

The error alert tab shows an error alert window after the incorrect number is entered.

Enter the title of the alert.

Enter the error message.

Error Alert

There is also an icon option in the Style list which is the Stop, Warning, and Information option to choose from.

Error Alert in Data Validation

Then click the OK button.

On the right corner of the cell, the message appears.


How to remove Data Validation in Excel

To remove the Validation Data in the Excel sheet you can follow these steps.

Select the data validation cells.

On the Data tab, press the Data Validation button.

In the dialog box of Data Validation in the settings tab click the Clear All button and then click OK.


This method is very helpful in removing Data Validation in Excel. You can also Go To Special option to select and remove the data validation cell.

In the next tutorial, we will learn about the Shortcut Keys in Excel.