Excel data form

  • Post author:
  • Post category:Uncategorized

When we input large data sets in Excel that spans across several columns, we are prone to commit mistakes such as encoding a value in the wrong column, or encoding the next entry in the wrong row.  Data forms effectively improve speed and accuracy in data entry.  

What is a data form?

A data entry form looks like this:

Figure 1.  Data Entry Form template

Note that there is a textbox for each criteria that we need to fill out.  

How to add Data Form in menu?

In order to create a data form, we first need to add the Form button on the ribbon by following these steps:

  • Click File Options
  • In Excel Options, select Customize Ribbon
  • In Customize Ribbon dialog box, enter the following:
    • Choose commands from: Commands Not in the Ribbon and select Form
    • Customize the Ribbon: Select Data and click New Group

Figure 2.  Adding a new tab in the ribbon

  • A new tab New Group (Custom) will be added under Data.  Click Rename and enter “Data Form” in the Display Name text box

Figure 3.  Renaming the tab

  • The new tab will be renamed “Data Form”.  Select the command Form in the left side and click Add.  

Figure 4.  Adding the Form command button

This will add the command button “Form” in the ribbon under the Data tab.

Figure 5.  Data Entry Form command button added

How to make a data form?

Suppose we have these column headers for our data.  

Figure 6.  Sample column headers

In order to make a data form, we follow these steps:

  • Select the range for our data and click Home tab > Format as Table

Figure 7.  Format As Table in menu options

  • Select a table style and tick “My table has headers”

Figure 8.  Format As Table dialog box

  • Click anywhere in our table and click Data tab > Form
  • A data form template will appear, with textboxes for each of the column headers in our table

Figure 9.  Output: Create a data entry form

How to input values through the data form?

The data form is very easy to use.  Enter a value in the first criteria and press the Tab key to move on to the next.   

Figure 10.  Entering values into the Data Form

When we press the Enter key, the data we have entered will be instantly added into our table.  The data form template will then display empty textboxes ready for our next entry.  

Figure 11.  Data added into the table

The form has several buttons on the right where we can add, delete or restore a record, or browse through the available records and criteria.