Join Dennis Taylor for an in-depth discussion in this video Short-list and long-list variations, part of Excel 2013: Data Validation in Depth.
As we look at the data on this worksheet, called Lists, in columns A, B, C and D, we're going to type in people's names in column A. But for column B, we don't want anybody typing anything, because we've got a list that's currently on this same worksheet, off to the right. If you provide a Drop Down list or a so-called Pick List, you speed up data entry, you ensure consistency and accuracy and nobodies going to misspell anything. As we look at other columns here for States, how many people know how to spell Massachusetts? And how about some of these Department names out here; Environmental is often misspelled, there's some other entires there too.
You could imagine people entries a little bit different than the next one. And at a later time when you're trying to make comparisons between different kinds of data in different locations, sometimes these are not going to match up. Think of how much time will be saved if people are not typing these kinds of entries. And, again, that added bonus there of getting consistency here. So, let's start with a small example. We want to say that in column B, the only entries that are allowed are these four entries. Now, initially, having these lists nearby is helpful. In the next movie we'll talk about how we can position these differently and also, control the order of entry, as well.
In column B, we're about to say the following, you must pick from a list and on the data tab, choosing data validation. On the Settings tab, under allow, the choice is List. If you click in the Source Panel, you don't have to collapse it or anything, just go out and highlight the cells in question. F 2 through F 5. Drag across them, when you let go of the Mouse, it appears right here in the Source Panel. Click OK. And we see the Drop Arrow here. Here's our choice. It's going to be half time, maybe down here it's going to be full time and so on.
Another option for entry here is you can press Alt Down Arrow. That activates the list, use the arrow keys up or down, come to the one you want, press Enter. When I teach this in live sessions one of the frequent questions is can you use the first letter. If I type the letter H, and I'm thinking of hourly here. Half time pops up, and the reason it does, is because it already occurs in the list. The whole idea behind these kinds of lists is not to type, you don't want to be typing anything. So, the idea is to use the Arrow key or Alt Down arrow, to make the entries.
If you want to add to the list later, I'll show you how to do that in the next movie. Let's talk about state also, in these entries here, we see them in column G, same general idea, same approach, different example. We'll just click column C, once again, Data Validation, allow List, click in the Source panel and let's go highlight the States. Just like that, and OK, and same general approach. The order of those States was by population, so presumably the most frequent entries appear first, and that's one approach to sequencing the data.
Same general approach. Here, using Alt Down Arrow probably isn't as efficient, although you could get somewhat faster with that approach. And one more example here in column D, here the emphasis might be on the fact that some of those entries are quite long, a lot of people misspell Environmental, and some people would not abbreviate Manufacturing, others might, someone might put a period behind admin, someone might spell out and instead of using the ampersand symbol down here. So once again we're ensuring consistency, and saving a lot of typing here. So, column D, same general approach, Data Tab, Data Validation, Allow List.
And this time the Source is this set of cells right here. And OK. So same general approach as before. There we have it. So, a great tool for ensuring consistency and saving a ton of time.
- Testing for whole numbers and decimals
- Using the input message box
- Sequencing and placing lists
- Creating multitiered lists
- Setting date and time limitations
- Limiting text length
- Locating data validation rules
- Requiring entries to be unique