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

Setting Up a Database in Excel 2010

Building and viewing data with the Form tool


From:

Setting Up a Database in Excel 2010

with Dennis Taylor

Video: Building and viewing data with the Form tool

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.

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

Building and viewing data with the Form tool

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.

Share a link to this course
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.

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
Welcome to the redesigned course page.

We’ve moved some things around, and now you can



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.

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