masthead for advanced excel

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.



facebook-pic"

New! Comments

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