To completely eliminate typing within a range, use the List option under Allow on the Settings tab in Data Validation. Define the location of cells containing all possible entries allowed in the range where you apply the rule. Once defined, a user need only click an arrow in the Validation range and make a selection.
- [Instructor] In the Chapter Two exercise file, the first worksheet is called Lists. We're setting up a list here in Columns A through D. For other users, for data entry purposes, we wanna make sure in Columns B, and C, and D, that the entries here occur much more fluently, much easier and with accuracy, by providing what are called Pick Lists, or Drop Down Lists. You don't see those terms officially in Excel, but they're very popular with a lot of Excel users. Anything that contributes to the idea I'm gonna be doing less typing, is generally favored by most users.
Let's imagine that in Column B we only want to allow four entries there. Now we're currently seeing these entries over in Column F. When you're setting up the Drop Down List, the Pick List that we're about to see, it's really handy to have the source data visible off to the right. Now, eventually, you might want to change the location of those. So, let's start with Column B here. And set up a data validation rule by way of a Drop Down List. Data Validation. And we click Settings if it's not already selected.
Allow List. Click in the Source Panel. And then highlight the cells in question. Not the title on top. But the entries that will be allowed here. And as soon as we click OK, we see the arrows appear here. We click an arrow. We make a choice. Next cell down. Make another choice. And so on, and so on, and so on. You can also use from the keyboard, Alt + Down Arrow. That exposes the list. Then use the arrow keys, come to the one you want, press Enter.
Now, a common question that I get when teaching this subject live is people say, can I type a letter and have the entry appear? And part of the reason they might think that is they might have done this in the past. For example, if I type the letter H right now, Hourly appears. Now, possibly, I would have wanted to enter Half-Time there, but the idea here is you don't type anything. The reason Hourly appears here, is because it already appeared above in the same column. If I want Half-Time to appear, it's not gonna help if I type Ha.
We see the Ha, but why type. The whole idea is let's not type. We can either use Alt + Down Arrow, or perhaps more likely for many people, just click the arrow, and there it is. We'll make the choice ourselves. Let's do the same kinda thing in Column C. The entries there are longer in some cases. And a lot of people don't know how to spell Massachusetts. And so we've got a list of states over in Column G. They're listed in order by population. That might be important if the kinds of names that we're putting over here are from the larger states. But, again, same general idea.
Let's select Column C. Data Validation. Allow. List. Click on the Source Panel. Let's highlight all the cells from G2 downward. And that's gonna save us a lot of typing time when we're entering states like District of Columbia, and New Hampshire and Massachusetts, and so on. The longer named states. The difference here would be that now we will see a scroll bar. When there are more than eight entries, we see a scroll bar. We can easily move up and down there. And we can move pretty fast too.
District of Columbia might be the first one. Of course, we'll make the column wider for those kinds of entries. Same general idea here, as before. Now, Alt + Down Arrow probably will not be as efficient here, but you can still use it. I'm holding down Alt. I press Down Arrow. There's the list. And then continue to press Down Arrow, and I'm holding it down now. So we can move through this pretty quickly. There we are, and so on. Same general idea as we saw before. And also in Column D here. Same general idea. And here we really will save a lot of time.
Look at some of those entries. And the other aspect of this to consider is that we will ensure accuracy and consistency. Nobody's going to misspell environmental. And nobody's going to put a period behind Admin if we don't want it to be. Here's our master list. We've got a period behind Mfg. Now, that's gonna prevent anybody from typing in Manufacturing for this kind of an entry here. Major Mfg period is the way it's going to be. We're going to be picking these from the list. And nobody will be able to type and if they're trying to make this entry.
There's that ampersand symbol instead. And some of these are quite long and lengthy and you can imagine the potential typos that would appear here, if we allow typing. So, same general idea in Column D. Here it's gonna be an even bigger time saver. Once again. Data Validation. Allow. List. We click on the Source Panel. Highlight the entries from H2 downward. There we are. And OK. And there we are.
It's probably better in some cases to make these alphabetized, they are here. It helps us find these entries a lot faster. So, a ton of time is gonna be saved in Column D. Also a lot of time of course, in the other columns as well. Nobody's typing anything here. And we can get to our entries quickly and easily. In the next movie you'll learn more about the placement of these. But, certainly when you're setting up the pick lists like these here, have the source data visible. And you wanna be able to check it out on a few entries just to make sure it's working properly. So these are valuable tools for eliminating some of that drudgery associated with lots of typing.
- Controlling numeric input
- Using the input message box
- Setting up pick lists for quick, error-free input
- Creating multitiered pick lists
- Setting date and time limitations
- Limiting text length
- Requiring entries to be unique
- Locating cells with data validation rules