Excel: Use a Built-in Data Entry Form

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

  1. The built-in data entry form.
    1. 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.