Microsoft Excel Logo

Manual data entry can be time-consuming and error prone. But if you take a few minutes to create a data entry form in Microsoft Excel, you can improve the process and reduce the risk of mistakes like missing data.

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.

RELATED: How to Create a Customized Tab on the Microsoft Office Ribbon

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.”

Pick 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.

Choose All Commands a pick the document

Advertisement

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

Select Form and click Add

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

Form button in 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.

RELATED: How to Create and Use a Table in Microsoft Excel

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.

Select a table style

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

Check the table has headers box

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

Table in Excel

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.

Advertisement

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

Open the data entry form in Excel

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.

Complete the form fields

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.”

Form buttons

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.

Edit the form

Advertisement

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.

If you use Excel on a Mac, you might also be interested inΒ quickly adding data by scanning a printed document.

RELATED: How to Insert Data from a Picture in Microsoft Excel for Mac

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio Β»

The above article may contain affiliate links, which help support How-To Geek.