Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Excel data form

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.  

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc