Restricting data entries with data validation
Video: Restricting data entries with data validationWhen you're creating a list or database, a great tool to accompany that is the idea that you want your data to be accurate. In other words you would like to validate it. Keep out the bad data before it ever gets into your database and that's almost rule number one in creating data. There are some validation tools. I am going to switch to a worksheet here called Validation and its showpiece feature is what we call a pick list. So the data off to the right may not be so obvious at first as to why it's there, but let's imagine a scenario here where we are building a small list here, potentially large of Employee Names, Building, Department, Status and Salary and to make this process a lot faster, it sure would be great if nobody had to type anything for Department or Building or Status, just put in the Employee Name and the Salary and pretty much have these fall into place automatically.
- Next steps
Viewers: in countries Watching now:
In this course, author Dennis Taylor shows how to use Excel for creating a simple database. The course explains the limits of Excel as a data management tool and spells out the design considerations for creating a database. It also demonstrates using tables to simplify database creation as well as the Form and Data Validation tools to manage data.
- Understanding database design concepts
- Using the Text to Columns feature
- Creating fields
- Using tables
- Adding total fields
- Formatting tables with the Design tab
- Using the Form tool to build and view data
- Using data validation to restrict data entries
Restricting data entries with data validation
When you're creating a list or database, a great tool to accompany that is the idea that you want your data to be accurate. In other words you would like to validate it. Keep out the bad data before it ever gets into your database and that's almost rule number one in creating data. There are some validation tools. I am going to switch to a worksheet here called Validation and its showpiece feature is what we call a pick list. So the data off to the right may not be so obvious at first as to why it's there, but let's imagine a scenario here where we are building a small list here, potentially large of Employee Names, Building, Department, Status and Salary and to make this process a lot faster, it sure would be great if nobody had to type anything for Department or Building or Status, just put in the Employee Name and the Salary and pretty much have these fall into place automatically.
And even with Salary, we might want to have some controls here, maybe on the list that we are thinking are we working with an organization and your top salary is 120,000, and for a while nobody is going to go over that, and they want to make sure that all the entries fall below that value, and maybe at the same time they've got a minimum of 30,000 or whatever. We want to put controls here to make sure that no entry in this column falls outside that boundary. So let's click Column E. Many, many times when you are setting up data validation rules, if you can select the entire column, it just smoothes the process, makes it easier, rather than highlighting specific cells.
On the Data tab in the ribbon, you will see a choice called Data Validation. We can allow anything here, that's the current setting, allow Any Value. For yearly salary I would imagine a Whole number probably be a sensible choice. If it does contain pennies, well, then use Decimal, use one of these two. And although, we won't be showing you other examples here, think of Date and Time and how you might have restrictions on those, or maybe sales within an organization only occur between 6 AM and 7 PM, something like that.
Think of certain kinds of ID numbers where they have a limited text length, there are other kinds of restrictions you might want to consider using. So on this example, let's say we use Whole number, and immediately we see this prompt, if we have only an upper limit or only a lower limit we could use some of these choices. Let's say we use between and the Minimum in this example here 30,000 and our Maximum is 120,000. If we wish we can even provide a message in a box that will appear in the cell when we click in any of these cells in Column E. So let's provide an Input Message.
Don't necessarily need a Title, but you could use a word like Reminder perhaps, putting in a colon, makes it look a little tidier and just a simple message saying, Must be between 30,000 and 120,000 and click OK. You can also provide an Error Alert too. I will show you that after this.
So as we click here that should be enough to keep out not a typo necessarily, but it's a clear reminder of what's supposed to go in here. So, of course, we will put in a sensible amount. But just to see what happens when we violate the rule, there is the pop-up. Now you even can control the wording here if you wish, that's under the other tab that we saw there. For the moment here, let's say, that okay, we made a typo, Retry, we will just change that. It should have been 33 and we are all set.
So, pretty basic idea and concept there to keep out the bad data. Now for Status, for Department, for Building, notice that on this worksheet off to the right are various lists of what can go in there. Now the idea behind a list is you don't want anybody typing any of these and you want these to be the only possible selections that can be made, and when you set up lists, at least initially, it's a good idea to have them on the same worksheet so you can see them and review then and make sure everything is working okay.
A pop up list that we will see in these can only show eight at once, so on a Department list, we are going to have to consider the order of these, and the fact that there will be a scrollbar. Let's just do one of the smaller ones, for example, Building for Column B. In Column B, we don't want anybody typing anything, we want them to pick from a list. Data Validation>Settings>Allow>List and let's just move this dialog box over, so we can see the list.
Source, click here, refer to these cells, click OK. So, and even though that's on B1, we'll ignore that. Click here, there is our list, click there, there is the next one. We are not doing any typing of course. If your hands are on the keyboard and you want to get to the list, you can press Alt+Down Arrow, then use the arrow keys, come to the one you want, press Enter. Similarly, we did the same thing with Status.
Now suppose we open a new building that's in this campus like setting here. So I'm going to right-click on any one of these, except for the top one, right-click and Insert and simply shift the cells downward. So we've got a new Building. It's called the Kenton Building. Put that in there. We don't have to do anything else with our definition. Next time we click the drop arrow, that's in the list, so we can easily do that. For Department, we are definitely going to have to make the column wider and you see the list over there in Column K. And not only are we going to save a ton of time here, how long it takes to type some of these, a lot of people will misspell certain words.
People will use abbreviations differently, if got different people doing this. Even you yourself might type these in differently at different times, abbreviations here and there, no periods, periods, that sort of thing. If you want a standardized list, here's where you do the standardization, and then nobody enters this differently. So that really helps too, not only the speed, but the accuracy here. One other aspect of this, this list sensibly is alphabetized. That makes good sense, because when we see the entries pop-up in Column C, we want to be able to find these quickly.
But what if you know ahead of time that a large percent or a significant percent of these is likely to be used more often than others? Maybe it's Manufacturing. Let's put this at top of the list. Now you can drag this entry with the Shift key that will move it and insert. I am going to put at the top of the list, in other words I'm violating the alphabetical concept, but because this is used frequently, I don't want to have to scroll to get to it. So, in Column C, just as we did before, let's apply Data Validation rule here.
Same idea, it's going to be a list. There it is. And where is that list? It's right here. Highlight the data, click OK. And as we use it, the list here, there is Manufacturing at the top of the list. We don't have to go scrolling for it, let's say, because we know we use it often. If we have to get to other choices though, they're alphabetized, so we should be able to find them relatively quickly. We did the same thing with Status. Now, it would make sense eventually to put this list somewhere else.
So what if were to move this list? Why don't we take this data and put it on another worksheet? Ctrl+X possibly, that's one way to do it. Put it over here on the Sheet3, right-click, Paste. There we go, it's over there. So what happens now? Click here, drop arrow, there is the list. If you're working in older versions of Excel, you actually have to give that list a range name.
You don't have to do that anymore, and so this makes it simpler and easier. Now there are lots of other examples with data validation, and I would like to refer you to another course from lynda.com on the Data Validation, both for 2007 and 2010. But there is no question; this is an appealing feature, particularly with regard to the pick list. You want to make sure that the data that goes into your worksheets is accurate, and data validation gives you a lot of tools for making that work.
There are currently no FAQs about Setting Up a Database in Excel 2010.