New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

Start learning with our library of video tutorials taught by experts. Get started

Setting Up a Database in Excel 2010
Illustration by

Restricting data entries with data validation


From:

Setting Up a Database in Excel 2010

with Dennis Taylor

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.

Watch this entire course now—plus get access to every course in the library. Each course includes high-quality videos taught by expert instructors.

Become a member
please wait ...
Setting Up a Database in Excel 2010
43m 27s Appropriate for all Oct 21, 2011

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.

Topics include:
  • 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
Subjects:
Business Databases
Software:
Excel
Author:
Dennis Taylor

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.

 
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.
Upgrade now


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 Upgrade now

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

Notes cannot be added for locked videos.

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.