Ease the Pain of Data Entry with an Excel Forms Template

Excel makes it easy to work with data tables, but first you must get that data into tables. Keying data into a spreadsheet and moving the cursor after each entry can be frustrating at best; at worst, it can leave you with mangled data in an unusable format.

Fortunately, Excel provides data entry forms to ease the pain. With only a dash of setup–and some secret knowledge about where to find the forms, which aren’t on the Excel ribbon–you’ll be entering data like a pro.

First, Set Up the Data Table

The first rule of Excel is that every minute spent organizing your data at the beginning can potentially save you hours of problem-solving later.

Organize your data in rows and columns (a “table”).

• Rows: Each row should represent one chunk of data, or one record. Depending on the nature of your table, a record might be one customer’s contact information or one invoice’s due date and amount due details.
• Columns: Each column should hold one type of information for each record, or one field. You’ll want to fill out this field the same way for every record. For example, if the table holds customer information, then one field would be the customer name, another would be the address, and a third would be the phone number.
• Row Headers: The first row of your table should be reserved for the column headers. Here you’ll put the names of the fields, such as “Customer Name,” “Customer Address,” and “Customer Phone Number.” Excel will use these as the labels for the data entry form.

With your data now organized, highlight the entire data table, click on the Home ribbon, choose Format as Table, and pick one of the table styles.

Now you’re ready to create the form.

Display the Forms Button

When Microsoft introduced the ribbon with Excel 2007, the designers chose not to include the form button. Before you can use it, you’ll need to add it either to the Quick Access menu or to one of the ribbons.

To add the form button to the Data ribbon:

1. Right-click an empty space on the ribbon and choose Customize the Ribbon.
2. In the dialog box that follows, set Choose commands from: to choose Commands Not in the Ribbon.
3. On the right-hand side, select Data and click the New Group button.
4. On the left-hand side, click Form…
5. Finally, with both Form… and New Group (Custom) selected, click the Add >> button.

After you click OK, you’ll find the Form… button on the far right of the Data ribbon.

Next, Set Up the Entry Form

This subheader is a bit of a misnomer because Excel will set up the entry form for you. You only have to click anywhere inside your table, then click the Form button, and Excel will present a dialog box.

The headers that you typed in the top row of your table are used for the field names. By default, the dialog box shows the first existing record in your table. You can browse and change the existing records with the Find Next and Find Prev buttons. To add a new record (row) to your table, click the New button.

When you’re finished, click the Close button, and you’ll see that Excel has made the changes to your table. With a little practice, you’ll find that this is an easier way to enter large amounts of data into your spreadsheet—and, once your table was in place, the only work you had to do was to click the Form button.

Customize the Table Entries

You can change the behavior of some of the form fields by changing the underlying data table.

• If you define a calculated field in your table, the form will calculate the result for that field and display it, but you won’t be able to change it.
• If you add data validation to a field, the form will apply that validation when you attempt to save the record.
• If you add data validation using a pick list, unfortunately the form does not include the pick list, although it will still require your entry to be one of the list values.