Use Excel Data Forms to Simplify Data Entry

Spreadsheets are valuable tools for organizing, manipulating, and analyzing data. But first you have to enter the data. This can be the least fun part of building a spreadsheet, especially if you have many columns of information that scroll horizontally off the screen. It can also be easy to introduce errors when entering or editing multiple rows of new data in a large sheet. Excel helpfully simplifies this problem with Data Forms.

Data Forms give you an easy way to enter or edit data, a row at a time, from a pop-up dialog box.

Let’s see how it works:

Start by setting up your new worksheet with column headers that describe the information you want to gather and any formulas and data validation you’ll be using to enter and analyze it.

These steps will apply to Excel 2007-2013. Images were taken using Excel 2013 on the Windows 7 OS.

To follow using our example above, download  Excel Data Forms_Template 1

Now, select any cell in the table and then click the Form button on the Quick Access Toolbar. Note: If you don’t see the Form button in your Quick Access Toolbar, follow the steps at the bottom of this article to add it.

You may see a pop-up warning if your data is not formatted as a table asking you to identify which row is your label row. If you have formatted your spreadsheet with column names in the first row, you will just have to click OK to continue.

Excel will automatically generate a Data Form that uses column labels as field names and data in the cells as editable fields.

Notice that columns such as “Total Order Amount” and “Difference” that contain formulas are not editable in a Data Form. Once you are using the Data Form you can:

• Edit existing data– Click the Find Prev or Find Next buttons on the form to locate the row you wish to edit, then make your changes in the correct field. Hit Enter on your keyboard to apply the change and move the next row, or Restore to undo your changes. (Once your changes have been Entered, you cannot Restore them, you will have to manually make the change.)
• Create a new record – Click New to clear all fields and add a new row of data to your spreadsheet. Hit Enter to add the row and create another new row, or Close to add the row and close the form.
• Find or Edit data that matches certain criteria – Click Criteria and then enter the search term in the field that you want to match. For example, search for a specific company in the “Company” field to find all the orders made by different people in the same organization. Hit Enter, then browse the results with the Find Prev and Find Next buttons.

That’s it! With Data Forms you can quickly enter multiple rows of data without having to jump all over the screen. You can also use them to find information quickly with search criteria, and edit information without worrying that you are on the correct row or column. Check out this template to start practicing right away!

Use Data Forms when you need to enter many new rows of information at once or when you need to edit several rows on a large spreadsheet such as:

• Enter contact information after a membership drive at the charity for which you volunteer
• Input sales leads acquired from an email campaign
• Update shipping rate information for groups of products

How do you use Data Forms?

How to add the Data Form button to the Quick Access Toolbar (QAT)

If you don’t see the Form button in your QAT, you may need to add it. Click the dropdown arrow on the QAT, then select More Commands. The Excel Options dialog will open.

Find the Form command in the left column, select it, then click the Add button. Then click OK. The Form button will now appear in the QAT.