The built-in Excel data entry form is ideal for data entry tasks. It shines when you need to enter data that has been arranged in rows. It is a time saver for the data entry gods. Most importantly, Excel creates this form automatically considering your data headings (you don’t need to know Excel VBA to create this form).
If you, therefore, have a lot of records to enter or edit, you can easily create the Excel data entry form dialog to help with this.
There are two easy ways of using this form in Excel, one involves using the keyboard shortcut (for the keyboard lovers), and the other involves adding it to the Quick Access Toolbar.
Excel Data Entry Form Shortcut
The following steps will guide you through the process of creating simple Excel forms using a keyboard shortcut.
- Click anywhere in your Data
- Press Alt+D and then press O (the letter O).
After pressing these hotkeys, the Excel data entry form dialog box will appear with your data entry fields.
Adding a form to the QAT
Another way to get access to this data entry dialog is to add it to your Quick Access Toolbar or add it to the Ribbon for easy access. Below shows how to add this command to the Quick Access Toolbar:
- Right-click anywhere in the ribbon and select Customize Quick Access Toolbar. The Customize QAT window appears.
- There’s a drop-down list called Choose commands from near the top of the window, click on it and select Commands Not in the Ribbon.
- In the list box on the left, search for Form and select it.
- Click the Add button to add it to your Quick Access Toolbar.
- Now click Ok to close the Excel Options window.
The Data entry form will then be added to your Quick Access Toolbar.
Therefore, anytime you need to use the form, you can either use your keyboard or the Quick Access Toolbar command.
How to use the Data Entry Form
The above tutorials only show you how to display the Excel Data Entry Form but not how to actually use it. Below is a blow-by-blow guide on how to use the form to enter data in Excel.
- Organize your data by entering column headings at the top row so that Excel can recognize it as a table.
- Select any cell in the data set or range and click the form button on your Quick Access Toolbar or use the shortcut Alt+D then O (letter O). Excel will display the form dialog which is automatically customized to your data.
- Click the New button to add a record.
- Click the Delete button to delete a record.
- Use the Find Prev and the Find Next buttons to navigate from record to record.
- The criteria button let you filter certain records to work with instead of the entire records. For instance, click Criteria, enter BOLID as the Customer, then click Find Next. Excel will now display only records of BOLID as you navigate through the records.
Pro tip: When a certain column contains formulas that automatically calculate as you enter data, that particular field in the data entry form will not be active since you don’t need to enter any data for it.