Excel Data Entry Form

Step-by-step instructions for entering data

screenshot/Microsoft

Using Excel's built in data entry form is a quick and easy way to enter data into an Excel database.

Using the form allows you to:

  • start a new database table or to add new records to an existing one
  • scroll through data records one at a time
  • search for records containing specific information
  • edit or delete individual records as needed

About Adding the Data Entry Form Icon to the Quick Access Toolbar

Using the Form to Enter Data in Excel
Using the Form to Enter Data in Excel. © Ted French

The data entry form is one of Excel's built-in data tools. To use it all you need to do is provide the column headings to be used in your database, click on the Form icon, and Excel will do the rest.

To make things more challenging, however, since Excel 2007, Microsoft has chosen not to include the Form icon on the ribbon.

The first step to using the data entry form is to add the Form icon to the Quick Access Toolbar so that we can use it.

This is a one-time operation. Once added, the Form icon remains available on the Quick Access Toolbar.

Finding the Data Entry Form Button

Access the Data Form in Excel
Access the Data Form in Excel. © Ted French

The Quick Access Toolbar is used to store shortcuts to frequently used features in Excel. It is also where you can add the shortcuts to Excel features that are not available on the ribbon.

One of these features is the data entry form.

The data form is a quick and easy way to add data to an Excel database table.

For some reason, however, Microsoft chose not to add the form to one of the tabs of the ribbon starting with Excel 2007.

Below are steps that will show you how to add the Form icon to the Quick Access Toolbar.

Add the Data Form to the Quick Access Toolbar

  1. Click on down arrow at the end of the Quick Access Toolbar to open the drop down menu.
  2. Choose More Commands from the list to open the Customize the Quick Access Toolbar dialog box.
  3. Click on down arrow at the end of the Choose commands from line to open the drop down menu.
  4. Choose All Commands from the list to see all the commands available in Excel in the left-hand pane.
  5. Scroll through this alphabetical list to find the Form command.
  6. Click on Add button between the command panes to add the Form command to the Quick Access Toolbar.
  7. Click OK.

The Form button should now be added to the Quick Access Toolbar.

Adding the Database Field Names

Using the Form to Enter Data in Excel
Using the Form to Enter Data in Excel. © Ted French

As previously mentioned, all we need to do to use the data entry form in Excel is to provide the column headings or field names to be used in our database.

The easiest way to add the field names to the form is to type them into cells in your worksheet. You can include up to 32 field names in the form.

Enter the following headings into cells A1 to E1:

StudentID
Last Name
Initial
Age
Program

Opening the Data Entry Form

Using the Form to Enter Data in Excel
Using the Form to Enter Data in Excel. © Ted French

Opening the Data Entry Form

  1. Click on cell A2 to make it the active cell.
  2. Click on the form icon that was added to the Quick Access Toolbar on page 2.
  3. Clicking on the form icon will bring up a message box from Excel containing a number of options related to adding headings to the form.
  4. Since we have already typed in the field names we want to use as headings all we have to do is Click OK in the message box.
  5. The form containing all of the field names should appear on the screen.

Adding Data Records With the Form

Enter Data Using a Form in Excel 2007
Enter Data Using a Form in Excel. © Ted French

Adding Data Records with the Form

Once the data headings have been added to the form adding records to the database is simply a matter of typing in the data in the correct order into the form fields.

Example Records

Add the following records to the database by entering the data into the form fields next to the correct headings. Click on the New button after entering the first record in order to clear the fields for the second record.

StudentID: SA267-567
Last Name: Jones
Initial: B.
Age: 21
Program: Languages
StudentID: SA267-211
Last Name: Williams
Initial: J.
Age: 19
Program: Science

Tip: When entering data that is very similar such as the student ID numbers (only the numbers after the dash are different) use copy and paste to speed up and simplify data entry.

To add the remaining records to the tutorial database, use the form to enter the rest of the data found in the image above into cells A4 to E11.

Adding Data Records With the Form (con't)

Using the Form to Enter Data in Excel
Using the Form to Enter Data in Excel. © Ted French

To add the remaining records to the tutorial database, use the form to enter the rest of the data found in the image here into cells A4 to E11.

Using the Form's Data Tools

Using the Form to Enter Data in Excel
Using the Form to Enter Data in Excel. © Ted French

A major problem with a database is maintaining the integrity of the data as the file grows in size. This requires:

  • fixing data errors or updating individual records.
  • deleting obsolete or duplicate records.

The data entry form contains several tools along the right-hand side that makes it easy to find and correct or delete records from the database.

These tools are:

  • The Find Prev and Find Next buttons – these allow you to scroll forward and back through the database one record at a time.
  • The Delete button – this is used to delete records from the database.
  • The Restore button – This button can be used to undo changes to a record that is being edited. Occasionally, we make the wrong changes to a record or even edit the wrong record altogether. If so, the restore button can be used to undo those changes.
  • Note: the Restore button only works as long as a record is present in the form. As soon as you access another record or close the form, the restore button becomes inactive.
  • The Criteria button allows you to search the database for records based on specific criteria, such as name, age, or program. An example of using the Criteria button is included in the next step of the tutorial.

Searching for Records Using One Field Name

Using the Form to Enter Data in Excel
Using the Form to Enter Data in Excel. © Ted French

The Criteria button allows you to search the database for records using one or more field names, such as name, age, or program.

Searching for Records Using One Field Name

  1. Click on the Criteria button in the form.
  2. Clicking on the Criteria button clears all the form fields but does not remove any data from the database.
  3. Click on the Program field and type Arts as we want to search for all students enrolled in the Arts program at the college.
  4. Click on the Find Next button. The record for H. Thompson should appear in the form as she is enrolled in the Arts program.
  5. Click on the Find Next button second and third time and the records for J. Graham and W. Henderson should appear one after the other as they are also enrolled in the Arts program.

The next step of this tutorial includes an example of searching for records that match multiple criteria.

Searching for Records Using Multiple Field Names

Using the Form to Enter Data in Excel
Using the Form to Enter Data in Excel. © Ted French

In this example we will search for all students who are 18 years of age and enrolled in the Arts program at the college. Only those records that match both criteria should be displayed in the form.

  1. Click the Criteria button in the form.
  2. Click the age field and type 18.
  3. Click in the Program field and type Arts.
  4. Click the Find Next button. The record for H. Thompson should appear in the form since she is both 18 years old and enrolled in the Arts program.
  5. Click the Find Next button a second time and the record for J. Graham should appear since he too is both 18 years old and enrolled in the Arts program.
  6. Click the Find Next button a third time and the record for J. Graham should still be visible since there are no other records that match both criteria.

The record for W. Henderson should not be displayed in this example because, although he is enrolled in the Arts program, he is not 18 years old so he does not match both of the search criteria.