Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Data validation rules help limit the data your users can enter into a cell, but they can be frustrating if the rules aren't clear. One of the clearest ways to require users to enter one of a set number of options is to define a list of acceptable values for a cell. As you can see in this workbook, I have a list of products where we have the ProductID, then the ProductName, and then the cell to which I want to apply the data validation rule, ProductCategory. Rather than force users to remember all the categories, and also how to spell them, you can provide a list, which I have here in these cells.
So my goal is to create a data validation rule where the only values that a user can enter into cell C2 is one of these values, and here is how you do it. First, you go to the Data tab and then click Validate. Then in the Data Validation dialog, you create a List type of rule. When you click list, the Source field appears, and you can click this button here. This is the Collapse dialog, or also the Selection button - you hear it called both things.
When you click it, the dialog box minimizes, and you can then select the cells that contain the values you want to allow in that particular cell, in this case C2. So I'll select Olive Oil, Gift Basket, all the way down to Books. It looks good, and then I will click the Expand Dialog button. You'll see that the reference to those cells appears in the Source field. Everything looks right. I'll click OK. You can tell that cell C2 has a List validation rule applied to it because of this list arrow here.
If I click cell B2, it goes away, but if I click cell C2 again, you'll see that this arrow appears to the side. When you click it, you get a list of values that you can enter into the cell. In this case, the Infused Lemon is Olive Oil. I'll click that, and there is the value. But what happens if someone tries to type in a value that's not on the list? Well, let's see what happens. I'll select the cell, and then I'll just type Tires, and press Return. When I do, you get a message box that says the value you entered is not from the required list. You then click Cancel, click the arrow, and you can select a value from the list once again.
Data validation lists are powerful tools that prevents spelling errors and limit your users' data entry to a list of approved values. That way, you can get the data you expect. Be sure to listen to your users though to find out if you need to add or edit items on the list to reflect their needs and the realities of your business.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 87378 Viewers
80 Video lessons · 136388 Viewers
59 Video lessons · 55032 Viewers
52 Video lessons · 68905 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.