In this guide, we’re going to show you how to enter data in form structure, without using VBA. Here are a few reasons why this might be a preferable option:
A spreadsheet’s tabular form is a great way to keep records and work on them. On the other hand, entering data manually in that tabular layout can be time consuming and error prone as you enter more data. Imagine that you are about to enter 1000th row on a 30-column table! A data entry form can help overcome this issue. You might be thinking that VBA and macros are the only way to do this, but thankfully, Excel has a built-in feature for this You can use Excel Forms to enter data in a form structure.
Adding Form Command into the Ribbon
Although this feature has been around since the 2007 version, it is excluded in the Form command from the Ribbon of Excel until the 2019 version. If you are using Excel 2019, you can find this icon under the Data tab.
For older versions, you need to add it manually. To do this, first open the Customize Ribbon section from Excel Options. You can easily open the dialog by right-clicking on the Ribbon and selecting Customize Ribbon item.
Once the Customize Ribbon section is open follow the steps:
- Add a new tab & group into your Ribbon by using New Tab button
- Find Form command under All Commands in the left panel
- Add Form command into the new tab/group
- Click OK to save the changes
Now, you are ready to use forms!
See data in Excel Form
If you already have a data set, click on any cell on that table. If you are about to create new one, write down the column titles, select them, and create an Excel Table with headings. Whatever approach you take, click the Form button in the Ribbon.
Excel Form dialog lists every field in a row in a vertical layout. Editable fields are listed as textboxes while formula-included ones are shown as labels. Once you submit the data, Form dialog show formula result as well.
You can navigate between records using the scrollbar or Find Prev / Find Next buttons on the dialog. If you want to use the keyboard, use the up and down arrow keys to navigate previous or next data. Alternatively Page Up and Page Down keys can be used to go 10 entries up or down.
How to enter data
If you want to enter a new data into your table, click on the New button. This action will clear all fields in the dialog. Alternatively, the form will be entered in new entry state if you try to access the next record after the last.
Fill out the fields, and use the Tab key to access the next field.
Pressing Enter or clicking the New button submits the new entry, and clears the fields one more time to allow a new entry.
Editing or removing an existing entry
You can edit any non-calculated values in the fields. Press the Enter key to apply your updates on the data.
Use the Esc key or Restore button to undo updates on the selected record.
Filter entries in Excel Form
You can filter which records are listed in an Excel Form based on criteria. Thus, you can narrow the work items and focus the ones you need.
To apply a filter, click on the Criteria button to activate the Criteria section of the dialog. This section is similar to the original Form. It has fields where you can enter criteria value.
Enter your criteria and execute one the following options to apply the filter:
- hit the Enter key
- click on Find Prev or Find Next
Either of these actions will take back you the Form section with selected record which matches the criteria. Continue to use Find Prev or Find Next buttons to navigate between filtered records.
Another helpful feature of the Excel Form is that you can use wildcards in criteria. For example, the following setting filters out records that Type field starts with any characters before the string “FLYING”, not only equal to “FLYING”.
Here is the Excel Form window after applied filter:
