Viewers: in countries Watching now:
In Excel 2010: Data Validation in Depth, author Dennis Taylor shows how to use the data validation tools in Excel to control how users can input data into workbooks and ensure data is entered consistently and accurately. The course covers creating dropdown lists, preventing duplicate entries, and controlling the format of numeric data, dates and times, and text entered into worksheets. Exercise files are included with the course.
In this worksheet there are three sets of data validation criteria. In column B we've got status entries. That list is currently in column J. You see it off to the right. Column C, state names. We see that off to the right in column K. And then column D for the Departments, that's out in column L to the right. When you set up data validation rules it's not a bad idea to have the data on the same worksheet so you can check out the results, but as a long-term solution this probably isn't the best location.
Depending upon the environment, you might simply want to hide these columns. Nothing wrong with that. Others who are using this particular worksheet might uncover them, possibly maliciously or by mistake. They could change the content. It may not be a good solution. Another approach could be to take these three columns, move them way, way off to the right, and then hide the columns. Better yet, why not put these on a separate worksheet? Now, in prior versions of Excel, if you did move a list like this to another worksheet, it wouldn't work properly until you gave them a range name.
It's still true, however, that lists that are used in data validation criteria must be within the same workbook. In other words, they must be in this file; they can be on other sheets. Prior versions require that if they were, you had to refer to them by a range name. So we're concerned about the location of the lists. The other concern is the order of the lists. Every time we choose a state here, we see the names this way.
We don't necessarily know how to find a state. Probably, it would've been better to have alphabetized these. Now if California entries appear very, very often--maybe it's not exactly in sync with the population-- but if California does appear most often, you might want to keep it at the top and alphabetize the remainder. Or based on your analysis of these three entries, if these first three states comprise the most frequent choices, keep them on top and then alphabetize what's left. So we want think of the order and we can change the order.
And a third concern is what if we need to add entries to the list? Or change the order of just an entry or two? Those are the concerns that we have. Let's first talk about the idea that where these are located can be changed. Let's use states for an example here. We don't want this list right here anymore. We're going to highlight it, and we can do this in a couple of different ways of course: right-click and cut, or Ctrl+X. I am going to put this on Sheet1, anywhere out here. Right-click and paste.
So it's out here now. So what happens to our pick list? Click here. There's the drop arrow. There's the choices. We're all set. Now, could we've referred to that location from the beginning? Yes, we could have. And so when you're setting up a list, you can refer to another sheet. We probably would want to move the other two lists as well. And again, they could be on the same sheet; they could be on different sheets. Remember, in prior versions of Excel you had to give them a range name first. And I could've done all three at once. So I'll just take these together, right-click, and Cut, and I'll go to that same Sheet1, and put them out here: right-click, Paste. There we go.
So they're over here now. They're not on the OtherList over here, and yet they still work properly. Now, how about the order of the lists? We can first with the Department list here. The most common choice in this list might be Manufacturing, but we have to scroll to it every time we need to use it. So let's get it at the top of the list. How can we do that? Keep in mind that we could have done this earlier in thinking out how the list should've been here.
We don't always have that option. But let's do consider, as we jump back to Sheet1, where the list is. We want Manufacturing on top of this. Now, you may know that you can drag a cell with the Shift key and insert it. So where do we want to put this? Well, as I drag upward--and I am holding down the Shift key--we want it on top, don't we? Unfortunately, if we put it on top, the cell reference is not going to follow. We need to put it right here, at least for the moment. So I am dragging top or bottom edge with the Shift key, putting it right here, letting go of the mouse first.
ADC, we're going to drag it downward using the Shift key. Put it underneath. Let's go check out that list and see how it's working now. There's Manufacturing at the top of the list. It works just fine. We might consider the same kind of thing with states as we suggested earlier. Let's go back to Sheet1 where I put this list and consider another aspect of how we adjust the list. We've added a new Marketing department. Where are we going to put it? If we put it at the bottom of the list, it will not appear in here. Let's type it here first, and maybe it's not going to have that many people.
We should be able find it simply by inserting it in its proper location. So we'll simply drag this upward with the Shift key and put it alphabetically right in there. It appears to have indenting also. I'll un-indent that. There we go. And how is our list going to look now? Marketing will be there alphabetically in the appropriate spot. The last entry here is Research/ Development, and that is the last entry in the list as we jump back here and check that out as well. Research and Development. You want to insert from within.
If one of these organizations no longer exists, you'll want to delete a cell-- not erase it or move, but simply right-click and choose Delete and then Shift cells up. So we are removing the group, International Clinical Safety, from the list. It's between Executive Education and Logistics. And now it's gone. Let's see what happens in our list here as we try and use it. It's not there anymore, as you would expect.
So the order of the lists, the placement of the lists, and the ability to add new entries and take out entries are critical features that we need to be thinking about if we're using the dropdown list capability of data validation.
There are currently no FAQs about Excel 2010: Data Validation in Depth.
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.