Create a Data Entry Form in Microsoft Excel - - Windows Tips and Tricks with Geek

Thursday, February 24, 2022

Create a Data Entry Form in Microsoft Excel

 Microsoft Excel Logo

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

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

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.

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.

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

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.

No comments:

Post a Comment