How to use data validation

Data Validation is a function provided by Excel to limit users from entering value other than those pre-defined. For example, you might want the user to enter only values between 0 and 200. You can set the limit using this function. In the following example, I have used it to limit users from entering values or text other than those in the list which I have pre-defined.

a. Select the cell in the worksheet where you want to limit user to certain values.

b. Go to Data and select Validation as shown in the figure below:

data validation - select from menu

c. In the dialog box, select the Settings tab. In the validation criteria, "Allow:" section, choose List.

d. In the "source:" section, enter the list of words or phrases allowed, each of them separated by a comma as shown.

data valiidation - set the limit

e. In the Input Message tab, you can enter a Title or a question and a message for the user.

data validation - prompt user with message

f. In the Error Alert tab, enter a title and message if the user does not follow your instruction. Click OK and you are done. Now users can only select No and no other answers.

data validation - enter the error message

Data validation is just one of the many excel functions. Click here to learn more.

New! Comments

Have your say about what you just read! Leave me a comment in the box below.