Start learning with our library of video tutorials taught by experts. Get started
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.
When you work with large lists or if you're starting to create a list or a database on Excel, you might want to use the Form feature. Strangely if you click the Data tab in Excel, you will not see the feature called Form, and it's been in Excel for a long, long time. And maybe Microsoft is sending us some message, maybe not. But if you want to use what we call a Form and its major advantage is you can see all of the information about one record all at once.
In this particular worksheet here, we need to scroll back and fourth, left and right to see this data, and maybe it's really important to focus on the data right now in row 4. And in, in other worksheets, for example, like this, maybe this is not so much of an issue, because we can see everything on the screen at once. It depends upon the nature of the data. If you want to use the Form feature, you don't have to do anything special. In fact, what you might have in, in fact, is just a few records. As long as you have a title row and at least one record, potentially you can use the Form feature.
But where is it and how do we get to it? If you right-click anywhere on the Quick Access toolbar, you'll see a choice Customize Quick Access toolbar. Once here, we want to choose by clicking the drop arrow, and choose either Commands not in the Ribbon, to get a list of about 300 entries or All Commands, doesn't make any difference, either of these two. And what we're looking for in here is the word Form, and there used to be a command called Data Form in Excel 2003, and in prior versions.
Here's the icon for it, click it and then Add. So we are adding the Form button to the Quick Axis toolbar, just click OK. So maybe starting with a worksheet that's got a small amount of data or maybe larger, doesn't make any difference. Let's go to this Form button and click it and here's what we see. Now we probably wouldn't need this particular feature right now, because we don't have a lot of data, although it's certainly not wrong to use it. And this is a nice way to see the information about just this one record at a time.
But let's show how this might look with more data. Here's the Employees list, click anywhere in here, click the Form button again, and we see how it's looking here. Something a little different here you'll notice, is that it looks like two of the fields here, you can't make any changes too. Now we didn't say you could make changes, but we certainly can on the others. This maybe isn't the fastest way to make a change here. Maybe it turns out that Irv Fleming here really isn't in the West Building, you know, you might as well change this out in the worksheet, but we could change it here if we want to, he is really in the Taft Building.
So we can make the change there. And you can see here, we can scroll it around to other records here. And as I scroll down into record 2 the Irving Fleming Building change actually took place out there in cell B2. But I'm not really making a strong case for saying this is a data entry vehicle. However, if we were to do add a new record, it would automatically calculate the month, and although it would be a little bit strange to calculate years for a New Employee, that would happen as well to. So suppose we add a new record here.
I'm just going to choose New, and I won't do much typing here, but I'll just put in somebody's name, Smith, Sam, and I won't fill any other details just yet, and I'll put in the Hire Date of 10/7/11, that's all I need for now and scroll down so maybe we will close this. So what will have happened at the bottom? I'll double-click the bottom edge here, there is Sam Smith, hired on this date, and this column here, which has the anniversary month, has all ready been calculated automatically.
So us this one too, but for the moment it's irrelevant, this actually calculates the number of years the person has been here. And it doesn't work so well on this one, because I actually put in -- I'm doing here a future date, so you can ignore that. Let's see what happens here with large amounts of data. This is the worksheet that's got quite a few columns in it. Let's choose the Form button here. Now this actually is the maximum number of columns we can use with this particular feature, and it's 32. And in this particular worksheet, it looks as if there are one, two, three fields for there formulas in place.
And so here too, if we were to add a new record, automatically those formulas would be copied downward. The Form is really useful here, so we can focus on one item at a time, one row, row one. We can click the drop arrow at the bottom, here's record two, record three, and so on. So that's the advantage, and look what happens here, if I decide to add more data. I've got some data setup here off to the right, so I'm going to delete column AG and pull in this information, because I needed to go along with this data.
So I'll simply Delete this. We now have 34 columns in this list, look what happens when I try and use Form. It just doesn't work and you'd think that maybe that it have a better error message than this. It doesn't say how many or too many, well, if it's over 32, it just won't work. So we can't put that data in here. We might want to consider getting rid of some other fields, so perhaps I'll come back and insert, and again, if we want to use this, fine, we can do this now. I think you can see how this feature works that has a distinct advantage in allowing you to see all the information about one record at a time.
It has some advantages for putting in new records. One other aspect of this which isn't a strong case to be made for it, but it still works; I think the filter will do this better. But if for whatever reason, we wanted to see all of the records that met a certain criteria. For example, we want to look at all of those who have a description here of job control. So we could go to the Criteria tab here and type in under Description here Job Control, so not all of the records have that particular entry there.
If we now choose Find Next, this take us to record two, next one, three, four, or may be there is a whole cluster of them there, but we're not necessarily going to stop at every single one of these. Looks like there are a lot of them there, but still, for the moment we're only looking at those that have Job Control in the Description panel. And we could have used multiple criteria as well there. Let's jump over to Employees here, and maybe using Form here we just want to view the hourly people.
So we'll go to Criteria, and here we'll put in under Status, put in Hourly, and along with that maybe choose Job Rating 5. In other words, we want to see on a record by record basis, just those people who are Hourly and have a Job Rating of 5. So we'll Find Next and there's the first record, record number 77, there is Michael Mosley, and there's the next one, 87, and so on. So some advantages there perhaps, although I think a filter would work better.
So the Form capability, a bit buried in Excel 2007 and 2010, nevertheless has some advantages for not only building data, but also viewing data, a record at a time.
There are currently no FAQs about Setting Up a Database in Excel 2010.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
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.