MrExcel.com - Your One Stop for Excel Tips & Solutions

Use a Built-in Data Entry Form

November 07, 2022 - by

Use a Built-in Data Entry Form

Problem: I need to do data entry in Excel. I have a lot of records to key or to edit. Can I easily create a dialog to help with this?

Strategy: By using Excel VBA, you can build very complex dialog boxes for data entry. However, even without knowing VBA, you can use a simple built-in dialog for entering data:

  • 1. Select a cell in your data. Press Alt+D then O (letter O). Excel will display a dialog box with your fields. Click the Find Next and Find Prev buttons to move through the data set.

The Data Form tool. You need to key data in to columna A through G and a formula in H calculates a total. Open the Form and Excel uses the worksheet name as the title of the dialog. The headings from row 1 become labels along the left side of the form. Any columns that contain constants will have a textbox. The columns with formulas will display the result of the formula. As you type in the textboxes, the data you type is entered in the grid. In this figure, you are seeing record 15 of 15. There are buttons for Previous Record, Next Record, New Record, Delete Record, Restore Record, Criteria, and Close.
Figure 808. The built-in data entry form.

  • 2. To add a record, click the New button. The Total field does not fill in until you click Find Next and Find Prev to enter this record. When you come back to the record, the total will be filled in.

Additional Details: The Criteria button will allow you to limit the Find Next and Find Prev buttons to only contain records. For instance, click Criteria, enter Joe as the Rep, and click Next. You will see only Joe’s records.

Gotcha: The Form command used to be on the Excel 2003 Data menu. Microsoft has removed this option. However, you can use the Excel 2003 access key Alt+D+O to load the data form. If you want to have an icon for Data Form, you can customize the Quick Access toolbar. In the Customize dialog, you look in the category Commands Not in the Ribbon.

This article is an excerpt from Power Excel With MrExcel

Title photo by Khara Woods on Unsplash