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.

Share this page:

What’s this?

Enjoy this page? Please pay it forward. Here’s how…

Would you prefer to share this page with others by linking to it?

  1. Click on the HTML link code below.
  2. Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.

<a href=””>Advanced Excel – From a Business Perspective</a><a href=””>Advanced Excel – From a Business Perspective</a>hiddensecretsbookcoverhiddensecrets_online

Excel Courses for Business Professionals

How to sleep and lose weight

Copyright © 2007 – 2019. All Rights Reserved. Privacy Policy

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation. is in no way associated with Microsoft

Popular Courses

Useful Links

Links I found useful and wanted to share.

Search the website