Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Restricting data entries with data validation

From: Setting Up a Database in Excel 2010

Video: 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.

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.

Show transcript

This video is part of

Image for Setting Up a Database in Excel 2010
Setting Up a Database in Excel 2010

12 video lessons · 23997 viewers

Dennis Taylor
Author

 

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now "Already a member? Log in

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Setting Up a Database in Excel 2010.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

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.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.