Does your job involve a lot of data entry? Is inputting data part of your daily work? Well, the good news is entering data into excel spreadsheet is faster and easier when you create Data Entry Form in Excel.

This tutorial explains what a data entry form is, how to use it and how to create a data entry form in Excel.

What is an Excel Data Entry Form?

Data Entry Form in Excel is a form which appears as a dialog box that displays fields for one complete record (row) in a data table. It allows you to input data into rows and columns in Excel without moving from one column to another column.

With a data entry form, inputting data can be done in a quicker and more efficient manner, while reducing the amount of error that can be insert into the records.

The great thing about data entry forms is that because of its vertical structure, it allows you to see all the columns of a record (row) in a large dataset all at once without repeatedly scrolling horizontally.

Excel data form comes with features that enable you to enter new data, edit and delete data, as well as a search function that is used to search existing entries and navigate through records, and it can take a maximum of 32 fields per record.

How To Use the Buttons in Excel Data Entry Form.

In the Data Entry Form, there are many different buttons that allows you perform various actions on the form. They are:

  • New: This allows you to enter new details to create a new records (rows). You can edit or add new details and press Enter to update the records
  • Delete: This will delete any existing record that is displayed on the form.
  • Restore: If an existing entry is being edited, the previous data in the form can be restored if you haven’t clicked New or hit the Enter button.
  • Find Prev: This button will scroll through the previous entries.
  • Find Next: This button will scroll through the next entries.
  • Scroll Bar: This button allows you to scroll through records individually by using the up and down arrows. Also, you can move between fields (columns) in a record (row) using the Tab key to move forward and Shift + Tab to move backwards
  • Criteria: This button allows you to also find specific records using the search criteria. Click on the ‘Criteria’ button to enter a search keyword into the Status field and then use the find buttons. Click on the ‘Form’ button to return to the form view.
  • Close: This button allows you to close the form view by clicking ‘Close’
  • Edit a Record: You can edit a record when it is displayed on the form. You will select the field to edit the text. Then press enter to save the new changes.

Now, to create an Excel form, you will need to select a cell range in the worksheet, create a table for the cell range and add titles to the column headers first.

So, let’s take a look at the steps to follow to create a data entry form in Excel.

Adding the ‘Form’ Command to The Quick Access Toolbar

The first thing you need to do when creating a form in Excel is to add it to the Quick Access Toolbar or ribbon.

This will enable you create a data entry form with just a single click.

By default, the ‘Form’ command in Excel is not available on the ribbon. So it will need to be added via the ‘customize the ribbon’ option.

To do this,

1. Right-click on any of the tabs in the Quick Access Toolbar

2. Click on ‘Customize Quick Access Toolbar’

3. In the ‘Excel Options’ dialog box, select ‘All Commands’ from the dropdown menu

4. Scroll down to go and select ‘Form’

5. Click on ‘Add’ button

6. Click ok

Now the Form icon will be added to the Quick Access Toolbar as shown below and you can then click on any cell in the dataset you wish to make the entry and click on the Form icon.

Create a Data Entry Form in Excel

After you have added the Form command to the Quick Access Toolbar, you can now follow these steps:

1. First, you will convert your data or cell range into an Excel Table. If it is a blank data table, make sure the columns have named headers (Titles).

2. Select any cell in the table. This will enable the Data Entry Form dialog box to open.

3. Now click on the ‘Form’ icon in the Quick Access Toolbar.

4. A Form window that contains the number of fields (columns) and records (rows) from your data table will appear. If the data table is empty, only blank fields will appear.

Now, a data entry form has been created. You can now enter records into the form.

To enter new data into the table, you will click on the ‘New’ button to open blank fields where you can enter new records.

5. Press enter to save the details into the excel table and go to the next record.

I hope you find this tutorial useful. Don’t forget to share your thoughts in the comment section.

1,449 Replies to “How To Create Data Entry Form in Excel”