Excel 2003 Data Entry Form

01
of 08

Using a Form for Data Entry in Excel

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

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 if necessary

See the related tutorial: Excel 2010 / 2007 Data Entry Form.

02
of 08

Adding the Database Field Names

Adding the Database Field Names
Adding the Database Field Names. © 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

03
of 08

Opening the Data Entry Form

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

Note: For help with this example, see the image above.

  1. Click on cell A2 to make it the active cell.
  2. Click on Data > Form in the menus.
  3. Opening the form will first 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.
04
of 08

Adding Data Records With the Form

Adding Data Records with the Form
Adding Data Records with the Form. © Ted French

Note: For help with this example, see the image above.

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.

05
of 08

Adding Data Records With the Form (Con't)

Adding Data Records with the Form
Adding Data Records with the Form. © Ted French

Note: For help with this example, see the image above.

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.

06
of 08

Using the Form's Data Tools

Using the Form's Data Tools
Using the Form's Data Tools. © Ted French

Note: For help with this example, see the image above.

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.
07
of 08

Searching for Records Using One Field Name

Using the Form to Enter Data in Excel 2007
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.

Note: For help with this example, see the image above.

  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 a 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 the tutorial includes an example of searching for records that match multiple criteria.

08
of 08

Searching for Records Using Multiple Field Names

Using the Form to Enter Data in Excel 2007
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.

Note: For help with this example, see the image above.

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 on the Criteria button in the form.
  2. Click on the age field and type 18.
  3. Click on the Program field and type Arts.
  4. Click on 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 on 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 on 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.