1. Home
  2. MS Office

Excel 2010: Data Validation (Restricting Data)

In Excel 2010, by using Data validation feature you can ensure the data-type integrity by enforcing users to enter valid data from the range you selected. You can also write your own input message that appears before entering data, it helps users to acquaint with valid entries and error message that appear in case of any invalid data entered.

Launch Excel 2010, open a datasheet on which you want to apply data validation rules.

For instance: In grading data sheet, we need to apply rule that marks must be between 0-100, and in an event of any incompatible input value, errors dialog appears.


To start off with applying rule on Marks column, navigate to Data tab, click Data Validation.

data tab

Data Validation dialog will appear. On Settings tab, from Allow drop-down list you can select any data type that suits your selected table data.


Now set Whole number from Allow list, from Data list select between, select range of data from Minimum to Maximum value as shown in the screenshot.

data validation

Now navigate to Input Message tab, enable Show input message when cell is selected option. This option automatically shows input message specified under Input message box. Now head over to Error Alert tab.

input message

Enable Show error alert after invalid data is entered option, this will show alert pop-up. You can select style of alert pop-up, from Style list. Enter the error message you want to show under Error message text box. Click OK to end the Data Validation Dialog.

Error alert

When you select the cell to enter data, input message will show up with the cell, instructing you to enter data according to the rule defined.

input messagxe

If you try to enter any invalid data, then the Error Message alert pops-up and prevent you from entering any invalid data.

error message 1]


  1. the office 2010 is weird.
    i try to make data validation then after i saved it n close the file.
    when i open it again, then the data is still there but the validation is gone.
    what happen with that?


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.