How to Use Data Validation in Excel

Data Validation in Excel: Hello friends, We'll discuss How to Use Data Validation in Excel. Data Validation means you can restrict the data or value that enter into a cell by the user. We can give the limitation on the data to the user it means user enter the data in the limited range. This article is very helpful to describe how data validation works in excel, create a message to the users to explain the kind of data allowed in a cell and create an alert message when user enter incorrect data.

What is Data Validation

Data Validation features provide to make sure that user enters a certain value into a cell. We can configure data validation to prevent users from entering a data or values that are not valid. We can also create a message to the user to define the format of the data that should be entered in the cell. If the user enters the invalid data or value in a cell then an alert message should be displayed in the spreadsheet window.

Steps for Apply the Data Validation

First, create a data or values on which you want to apply Data Validation or restrict the invalid data. 

For creating Data Validation rule follow some steps:

• First, select a cell as output cell.
• Click on the Data tab then click on the Data Validation.
• Then go to the setting and fill the validation criteria.
• In the Arrow list, choose the option according to the requirements.
• In the Data list, choose the option.
• Enter the minimum and maximum value according to you.
• Clik OK.

In this example, we restrict users to enter only 10 digit mobile number. For this Data validation, we choose text length in allow list and choose between in the data list and enter 10 in the maximum and minimum option and click OK. The user can see this limitation message in the output cell and if user enter value instead of requirement value then an alert message also display on the screen for notifying that enter a valid value.

Create an Input Message 

Input message display when we select the output cell and tell the user what data or value to enter.

To create an Input message follow some steps:

• Click on Input Message tab.
• Check "Show input message when a cell is selected".
• Enter a title in the title box.
• Enter the input message.
• Click OK.

In this example, we want to show Enter a 10 digit valid Mobile no as an input message in the cell. For that, we enter Mobile Number in the Title and enter the full display message in the Input Message.

Create an Error Alert Message

Error message works when a user enters an invalid data or value, an error alert display on the screen. To create an Error alert message follow some steps:

• Click on Error Alert tab.
• Check "Show error alert after invalid data is entered".
• Enter a title.
• Enter Error message that displays on the screen after entering invalid data.
• Click OK.

In this example, we want to enter a 10 digit mobile no in the cell. If the user enters an invalid mobile number then an error message "Please enter a valid number" display on the screen.

Data Validation Result

For checking the Data Validation response, First select the cell where you want to enter the value. Here you see an input message that shows the valid data message .

Here a message display in the cell means this message indicate to the user to enter a 10 Digit valid mobile number. 

If a user enters an invalid data then an error message display on the screen and tells the user to input the valid data.

Here user enters a 9 digit mobile number that is invalid so an error alert display with a message "Please enter a valid number" so you can click on retry option and enter the valid number.

Conclusion:

Thus, in this post, you will find how to use Data Validation in Excel. It is the better way to restrict the value or data. If you like this post then you can share with your friend, colleagues, and relatives. We'll update this article on the daily basis. You can also share this article on facebook, twitter, Google+ and other social media website. If you have any query regarding this post then you can freely write in the comment section. We'll reply to you as soon as possible.