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

Building and viewing data with the Form tool

From: Setting Up a Database in Excel 2010

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.

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.

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 · 23854 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.