Join Dennis Taylor for an in-depth discussion in this video Using validation tools, part of Excel 2016 Essential Training.
- Excel's data validation feature is different than a lot of other data management features in that it's mostly about data before it goes into a worksheet, not after. And we're looking at a sheet called DataValidation in our workbook called 12 Data Management Features. Let's imagine we're about to create a list here, we've got an ID number, Compensation, Status, Department, the ID numbers must be six characters, they might be all numbers, they could be all letters, they could be a combination of either.
Let's say it's a combination of letter and numbers in our example here, let's select column A. Before using the data validation feature, highlight the cells where you want the rule to be in place. These are referred to as data validation rules. We go to the data tab in the ribbon and we have a choice called Data Validation. "Pick from a list of rules to limit the type "of data that can be entered in a cell". We're controlling data before it goes into a list. The Data Validation dialog box has three tabs, a Settings tab gives us some different choices.
Text length, whole number, decimal. We might want to choose whole number in some case if it were all numbers. We could set it to a certain length or make it between a certain set of numbers, something like that. If it's a mix of letters and numbers, as it might be in this case, we can choose text length and this will allow numbers as well, then we can set this to be between certain ranges or exactly equal to, we're going to say equal to here. Maybe in this case it's seven, now that means it has to be exactly seven.
If the ID numbers vary, say between five and seven, we would say "between" in the dialog box, like this, and put in a five and a seven. Let's say in this case it's exact, "equal to," and we'll make it be seven. So we're setting up a rule for here, we can even provide an input message. There's a box right here and we can give it a title if we want, and we could say "Caution, Warning, Reminder," something like that. We can put in a colon if we wish, not necessary, and then a description, "Must be exactly "7 characters long," we can be more specific and say something like it must begin with a letter.
Now maybe most of the people doing the data entry know that so we don't necessarily say that. If you provide an input message, and it's optional, you'll see that any time you click in the cells that you selected ahead of time. "Must be exactly 7 characters long." Well let's try this with only five characters. We get a pop-up message, "This value doesn't match "the data validation restrictions defined for this cell." If we click Retry, we're still in edit mode, if we click Cancel we're out of here.
So I'll click Retry and maybe I just forgot to type in that final two characters there, the 12. Looks good now, Enter, it's acceptable. Too many characters, doesn't work, now it could be all numbers, that's acceptable, it could be all letters, that's acceptable too. The only restriction we're saying here is seven characters long, so I think you can see how that will work in different situations. Now, the compensation numbers here, the salaries for example, might have an upper limit, they might have a range limit.
Maybe everybody in this organization is below 125,000, maybe that's for now the top total, we don't want any typos here, nor do we want any malicious entry here of higher values. So let's put a restriction here by way of data validation. Settings, this time Allow, now possibly with salaries it could be whole numbers or decimals, either one. How about whole numbers here, let's restrict these either within a certain range, minimum, maximum, or maybe in our example here we just want to make sure that these are less than, possibly less than or equal to, either way, the maximum, which is 125,000.
We could provide an Input Message, I won't do that this time, just click OK. So that's OK, that's acceptable. That's not, same idea. You also have the option here of putting in what's called, by going back to Data Validation here, you could put in an Error Alert that describes why that was unacceptable. And sometimes people like this because it looks like they know how to tweak the software, so I'm going to put in a message here, "INCORRECT." And I might follow it with a bunch of explanation points to get attention and the error message is, "Must be under 125,000" or less than or equal to depending on how you might say it or what the exact number is, there we go.
So next time we make a mistake here, we get this message. And again sometimes people do (laughs) get a little bit sarcastic or cute or funny in these messages, it's kind of fun to play with possibly, but that's not the key for us of course, the idea is we are controlling what goes into the column here. Now, a showpiece feature, data validation, is the idea of a pick list and people love this because they're not typing, they're going to pick from a list. Now right now we see two lists, one in column G, one in column H.
Eventually we will want to move these out of the way, but when you're setting up this pick list idea, keep the data visible so we can check it. In column C we're going to say, in effect, nobody types anything, let's go to Data Validation. Settings, Allow, and we don't see the term "pick list" but we do see "list." And where's the source of that list? Click in the Source panel here and go highlight the data, right there. There it is, OK.
Two ways to use this, we can click here, here's a drop arrow, we pick the one we want, that's all we do, down here, same idea. Different person, so on. If you're using a keyboard, if you've come here, press Alt + Down Arrow, you see your choices, come to the one you want, Enter, we can do it that way too. Now, what if we add a new item to the list? I'm going to put the word "Intern" in here, this is a new category, as you might expect this is not going to be in the list just yet.
We don't see it over there, but we can easily drag this upward with the Shift key. In other words we point to any edge, hold down the left mouse button and the Shift key, drag this upward, let go of the mouse first. Now, it will appear before "Contract" and if that's what you don't want, in other words it's before "Contract" but if you want "Contract" above it, then after having done what we just did here, drag this upward, the word "Contract," select the cell, drag from the upper edge with the Shift key, up here. And now it will be in the order that you wish.
If we have more than eight entries here, we will see a scrollbar, and let's say for the Department column, that's where we're headed. Now, think of the huge time savings here we're going to have by not having to type some of these entries, and furthermore we will have a lot of accuracy, or consistency perhaps is the better word here, we're not going to see different spellings of "Environmental" here, all those bad spellings that we sometimes see. And nobody's going to put period behind "Mfg" unless that's in the list here and it's not.
Or "Admin" and people will not use the word "and" here, it won't be acceptable. The problem here might be, we are going to have a scrollbar and we've got lots of entries. What if we know ahead of time that the manufacturing group is dominant here. Maybe up to 20% of these entries are going to be manufacturing, now we haven't seen the scrollbar just yet but we're going to have to go scrolling to find that every single time. If we use this as our source, in its current form, every time we see that drop list over here, that pick list, if we need to get to manufacturing, we're going to have to scroll to find it.
So let's move "Manufacturing" to the top of the list first. All the others of course are alphabetized and that makes good sense too because you want to be able to find the others. Most of them we need to find, manufacturing is there frequently. Maybe you also know that "Operations" is going to occur very often, drag that upward with the Shift key, so we're sort of saying in effect, alphabetize this but make a few exceptions, put these at the top so we can get to them quickly and easily. So in column D we're going to do the same kind of thing, select column D, Data Validation, Settings, List, Source, let's go highlight the data in column H.
OK, now in column D, drop arrow, there's "Manufacturing." Remember we put it there because we use it frequently, we don't have to slide very far to get to that, there it is. Excel down, maybe it's "Professional Training Group" whatever, it's out there, it's alphabetical so we know how to find it relatively quickly, there it is. Over time you might again consider re-ordering this, remember if later you realize, "We've got a lot of "logistics people too, and I don't like to have to scroll "to get to that all the time when I need it," let's drag this one up here also.
So you'll be the judge as to how often you want to change the order of this, but now "Logistics" is in the third position, meaning we don't have to go very far to get to it, there it is right there. A great feature, now eventually you would want to move this somewhere else, the idea is maybe others are using this, you don't want to leave this sitting out here where others might be able to change it. For some environments all you need to do is hide the columns, it's probably not the best thing to do, other situations it might be OK simply to move this way off to the right, but possibly we could just put this on a separate sheet.
Click plus right here, we've got a new sheet, come back here, take this data, Ctrl + X to cut, that's one way certainly, then go to the new sheet, click out there, Ctrl + V, Excel will have followed it properly. Let's come back to DataValidation just to check it out. Click here, drop arrow, we've got our list. If you're worried about somebody clicking on Sheet2, you could hide that sheet, right click and Hide. It's still going to be working effectively, DataValidation, come back here, drop arrow, we still have our list, so a very efficient tool.
We've seen some variations on how to use it, a really powerful tool for controlling data before it goes into a worksheet. Data Validation, found on the Data tab.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros