Excel Data Entry Form

Step-by-step instructions for entering data

By

Updated on July 11, 2019

An Excel data form is a convenient way to enter data into an Excel database and to view a row of information in a worksheet without scrolling through the sheet. Data forms list the column headings in a spreadsheet so that an entire row of data can be viewed at one time. Data forms are also used to search for records and edit existing records.

Instructions in this article apply to Excel 2019, 2016, 2013, 2010; and Excel for Microsoft 365.

Add the Data Entry Form Icon to the Quick Access Toolbar

The first step to using the data entry form is to add the Form icon to the Quick Access Toolbar. This is a one-time operation. Once added, the Form icon remains available on the Quick Access Toolbar.

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.

To add the Form icon to the Quick Access Toolbar:

Select the Customize Quick Access Toolbar dropdown arrow.
Choose More Commands.
In the Excel Options dialog box, make sure that the Quick Access Toolbar tab is selected.
Select the Choose commands from the dropdown arrow and choose All Commands.
Scroll through the alphabetical list of commands and select Form.
Select Add.
Select OK to add the Form button to the Quick Access Toolbar.
You'll find the Form button at the right end of the Quick Access Toolbar.

Add the Database Field Names

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

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

To follow along with this tutorial, open a blank Excel worksheet and enter the column heading data into cells A1 to E1 as shown in the image below:

A screenshot showing sample data in an Excel worksheet

Open the Data Entry Form

After your worksheet contains the column headings, it's time to open the data entry form.

Select cell A2 to make it the active cell.
Go to the Quick Access Toolbar and select Form.
In the warning dialog box, select OK.
The form containing all of the column headings appears on the screen.
Now you're ready to use the form to enter data.

Add Data Records With the Form

Adding records to the worksheet is simply a matter of typing the data in the correct form fields.

Add the first record to the worksheet by entering the data into the form fields next to the correct headings:

StudentID: SA267-567
Last Name: Jones
Initial: B.
Age: 21
Program: Languages

A screenshot showing records added to an Excel Data Entry Form

Press tab to move from field to field. Select New to add the record to the worksheet and to clear the fields for the second record.

Add the second record to the worksheet:

StudentID: SA267-211
Last Name: Williams
Initial: J.
Age: 19
Program: Science

A screenshot showing data entered into a Data Entry Form in Excel

Select New when you've finished adding the record.

When entering similar data such as the student ID numbers (only the numbers after the dash are different), use copy and paste to speed up data entry.

To add the remaining records to the tutorial database, use the form to enter the data shown in the image below into cells A4 to E11.

A screenshot showing Excel worksheet data that was entered with the Data Entry Form

Use the Form's Data Tools

As more records are added to the worksheet, it's important to maintain the integrity of the data. Keep data up-to-date and relevant by fixing data errors, updating records, removing obsolete records, and deleting duplicate records.

The data entry form contains several tools that make it easy to find, correct, and delete records from the database. These tools are found on the right side of the form and include:

Find Prev and Find Next: Scroll forward and back through the worksheet one record at a time.
Delete: Delete records from the worksheet.
Restore: Undo changes to a record. Restore works when a record is displayed in the form. When another record is accessed or the form is closed, Restore becomes inactive.
Criteria: Search the worksheet for records based on specific criteria, such as name, age, or program.

Search for Records Using One Field Name

Here's how to use the data entry form to search the database for records using one column heading, such as Last Name, Age, or Program.

In the data entry form, select Criteria.

When you select Criteria, the form fields are cleared from the form; no records are removed from the worksheet.
Place the cursor in the Program text box and type Arts to search for all students enrolled in the Arts program.
Select Find Next
The data for the first record that matches the search criteria appears in the form.

To make changes to the record, delete the existing text and enter the correct data.
Select Find Next to display additional records that match the search criteria.

Search for Records Using Multiple Field Names

The form can also be used to search for records using multiple criteria. For example, to search for students who are 18 years of age and enrolled in the Arts program. Only records that match both criteria display in the form.

Select Criteria.
Place the cursor in the Age text box and type 18.
Place the cursor in the Program text box and type Arts.
Select Find Next.
The data for the first record that matches the search criteria appears in the form.
Select Find Next to display additional records that match the search criteria.