A lot of people prefer a form entry view to a table view for data entry. Forms for data entry are something we are all used to seeing in popular programs like Filemaker and Access, but also for something as basic as filling out an online form… that is data entry too, after all.

There are ways to accomplish this task with macros, but there is an even simpler way to activate a new view involving Tables.

Exercise File

If you would like to follow along, download the exercise file here:

ExerciseFormEntry

This looks familiar if you have attended Advanced Formulas training. We use this same data to calculate age from birthday and grade from a grade percent. Notice that columns E, G and H all contain formulas, while the other columns are free entry.

Important: Format as a Table

This data has been formatted as a Table. We talk about tables at great length in Excel Essentials. Remember how I said there are endlessly cool things that will happen in your life when you format data as a table? Here is another one!

Creating a Form Button

Form entry is not a command you can find in the ribbon, but there is a work around for this. You might recall that the Quick Access Toolbar (QAT) contains all the commands you can find on the ribbon and also commands not in the ribbon… let’s find this one.

1. Find the Quick Access Toolbar (QAT) above the ribbon.
2. Select the dropdown arrow at the right side of the QAT.
3. Select More Commands.
4. On the Choose Commands From dropdown, select Commands not in the Ribbon.
5. On the left pane, scroll down and select Form (features are listed alphabetically).
6. Press the Add button between the two panes.
7. Form will appear on the right pane.
8. Press OK

A new button will appear in your QAT for Form entry.

Using the New Button

Let’s test out the button.

1. Place your cursor anywhere inside the table.
2. Press the Form button you just created in your QAT.
   
3. A form entry box will appear.

New: Adding Entries

1. On the right side of the form view, press New
   
2. Enter in your own information (give yourself a good grade!)
3. Notice how the columns that contain formulas (E, G and H) are not enterable.
4. Tab to move between fields
5. Press enter once data is entered.
6. Scroll down to look at the new entry in the table.

 

Criteria:  Search for a Specific Field

1. On the right side of the form view, press Criteria.
2. Place your cursor in the Last Name field and try searching for Last Name: Green
3. Press Find Next to be taken to the next entry.

Possible Pitfalls

• If you are sharing a workbook with a group of people who would like to do form entry on your table, they will need to add the Form tool to their QAT too. They will still be able to type into the table without the form view, of course.

Thoughts?

What do you think? Do you have any old tables lying around that could use this handy feature?

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

• Hannah Bates