Creating a data-entry form in Excel

Q. I spend a substantial amount of time entering data into Excel. Do you have any suggestions on ways to make this faster and/or more efficient?

A. One of the best data-entry tools to use in Excel is a data-entry form. This option is not very well known to most users because it is not included in the default ribbon. The use of a data-entry form makes data entry faster and more efficient because you don’t have to go to multiple different cells to enter data, row by row. Also, you are less likely to enter data in the wrong place. It is particularly beneficial to use data-entry forms when your data range or table has many columns.

A standard data-entry form in Excel looks similar to the screenshot below. (To follow along, download this Excel file.)

Even though creating a data-entry form is not part of the default ribbon, it is easy to create one. If you just want to create a form for a one-time use, click in any cell of your range or table and click Alt+D+O (or, if you hold down the Alt key, Alt+D+O+O). This will bring up a form similar to the one shown in the screenshot above. However, you will need to do this again anytime you need another form.

You can also add a data-entry form option to your Quick Access Toolbar (pictured below) by going to File, Options, Quick Access Toolbar. Under Choose commands from:, select Commands Not in the Ribbon. Select Form and click Add>>. Click OK.

Now, click in any cell of your range or table and click the form icon on your Quick Access Toolbar, as indicated below.

Using either option described above will bring up a form similar to the one shown in the first screenshot. For the example used in this illustration, the title “Loans” in the upper left corner of the form is the name of the worksheet, and the labels next to each field are the header names in the range or table. Notice the Loan Amount field does not display an input box. This is because this field is a formula and is calculated based on the other information that is input.

The buttons on the right side of the form are:

New: Add a new record.

Delete: Delete the current record.

Restore: Restore the record to its original data.

Find Prev: Go to the previous record.

Find Next: Go to the next record.

Criteria: Allows you to choose specific records.

Close: Close the form.

Once you have opened a form using one of the two options described above, click New to add a new record. All the fields in your form will become blank, and you can begin typing in the data for the new record. Press the Tab key to move to the next field. Once you have entered all of the fields, press Enter or click Close to save the record and close the form, or press New to save the record and open a new blank form for the next record.

You can also search the range or table for records based on specific criteria. For example, to find all records where the Sales Agent is Roberts, open a form, click Criteria, and type Roberts in the Sales Agent field, as shown in the screenshot below. Press Enter.

Note that the range/table does not display only the records with the Sales Agent Roberts. Instead, the records listed within the form are now only those records with the Sales Agent Roberts. When you click through the records within the form using Find Prev or Find Next, only those records with the Sales Agent Roberts will be shown.

You can also search the range or table for records based on multiple criteria. For example, to find all records where the salesperson is Roberts and the down payment is at least $80,000, open a form and click Criteria. Type Roberts in the field labeled “Sales Agent” and type >=80000 in the Down Payment field. Press Enter. Then press Find Prev and Find Next to scroll through the records that meet both of those criteria. In the example here, you will find two records meeting the criteria.

Another way to search for specific records is by using comparison criteria. For example, to search for only records where the customer’s last name begins with “Ab,” open a form, click Criteria, and type Ab in the Customer Last Name field. Press Enter.

Wildcard characters can also be used to search for specific records. A question mark represents any single character. For example, to find all records where the Sales Agent is Allan, a wildcard should be used because the name is sometimes misspelled as “Allen.” To search based on this criteria, open a form, click Criteria, and type All?n in the Sales Agent field to find both “Allan” and “Allen.” Press Enter. An asterisk represents any number of characters. For example, to find all records where the Loan Status is In Review, a wildcard should be used because sometimes the status is stated as In Review and sometimes it is stated as Review. To search based on these criteria, open a form, click Criteria, and type *Review in the Loan Status field to find both “In Review” and “Review.” Press Enter.

About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an assistant professor of accounting at Middle Tennessee State University.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.