Create a Data Entry Form in Microsoft Excel

 

Add the Form Option

To use the Form option in Excel, you’ll need to add it to either the Quick Access toolbar or ribbon. The simplest of the two is the Quick Access toolbar. If you decide to add it to your ribbon instead, you’ll need to create a special tab for it, which may not be ideal.

To add the Form button to your Quick Access toolbar, click the arrow in the toolbar to open the Customize Quick Access Toolbar menu. Select “More Commands.”

Confirm that Quick Access Toolbar is selected on the left. In the “Choose Commands From” drop-down box on the left, choose “All Commands.”

In the “Customize Quick Access Toolbar” drop-down box on the right, choose whether you’d like to add the Form button to all documents or your current one.

Scroll through the All Commands list and pick “Form.” Click the “Add” button to add it to the toolbar.

Click “OK” to close the settings and return to your spreadsheet. You should see the Form button in the Quick Access Toolbar.

Convert Your Data to a Table

To use the form, you’ll need labels for each field. These come in the form of table headers. If the data is already formatted as a table, you’re one step ahead and can move on to using the form. If not, you can easily convert it.

Select the cells containing the data. Go to the Home tab and the Styles section of the ribbon. Click “Format as Table” and choose a table style.

In the Create Table pop-up window, confirm the cell range and check the “My Table Has Headers” box. Click “OK.”

You will then see your data formatted as a nice and neat table.

Use the Data Entry Form

Now all you have to do is start entering data into your form! Select any cell within the table and click the Form button you added to the Quick Access Toolbar.

You’ll see a pop-up window with your table headers as the field labels. Fill in each field.

You can move between the fields and onto the next record quickly. After you fill a field, press your Tab key to move to the next one. When you finish completing all fields for the record, press Enter. Then fill out the next record. As you enter the data, you’ll see your table fill.

Use can also use the buttons on the data entry form to add, review, or remove records.

• To create another record, click “New.”
• To move through the records, click “Find Prev” or “Find Next.” You can also use the scroll bar in the data field area.
• To see the number of records, look above the New button.
• To remove a record, make sure it displays on the form and click “Delete.” Then confirm by clicking “OK.”

When you finish with the data entry form, click “Close.” You can reopen it any time by selecting a table cell and clicking the Form button in the Quick Access Toolbar.

Edit Your Form

If you’d like to edit the labels or rearrange the fields on the form, you’ll do so in your Excel sheet. Close the form, make the change in your table, then reopen the form. You’ll see your changes applied to the form.

For a fast way to enter a lot of data into a spreadsheet, especially when it’s from an external source, try creating your own data entry form in Excel.