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:
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.
e. In the Input Message tab, you can enter a Title or a question and a message for the user.
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.
Have your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- 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=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft