Are you tired of tedious data entry in Excel? Discover the power of Excel data forms, a tool designed to speed up your work and reduce errors. Imagine focusing on one entry, making it easier to enter or copy and paste information. In this tutorial, I’ll show you how to create a data entry form in Excel so you can enjoy a more efficient data entry process.
I’ll admit Excel data entry isn’t difficult, just tedious. You type in one cell and move to the next. However, I had a project where each record spanned past my monitor’s width, so I had a horizontal scrollbar. And several cells contained a paragraph of pasted text.
I wanted an Excel data entry form that would allow me to focus on one entry. This would also make it easier to copy and paste information from a source document into the required textbox.
The problem with using the default data entry method is that it’s easy to lose sight of your target. You also have to navigate to the next row. An Excel form can solve these issues.
What is an Excel Data Form?
A data form is simply a modal dialog box with the fields for one record. The form sits on top of your Excel spreadsheet. It allows you to enter up to 32 fields per record on a cell-by-cell basis. On the right side, you have a series of navigation buttons and functional buttons. You might think of it as an on-screen form or template designed for data entry.
Your column headers become the form field names. This is different than a custom Excel form a developer might create using VBA. You don’t need to do any coding.
Five Main Differences with the Excel Forms
- The form isolates one record or row.
- Your orientation is vertical, not horizontal.
- Some fields have keyboard shortcuts allowing you to jump within the form.
- You have a series of Form buttons on the right side to control navigation and filters.
- The whole record is committed as opposed to each cell.
Using the form, I can enter data faster, and when I Tab to the end of the form, I can hit the Enter key to start another record. I can navigate through the records either using the controls on the right side of the vertical scrollbar.
How to Create an Excel Data Form
In this example, I’ll create a form based on an existing worksheet with 6 fields. Once the form is created, I can use it to add or edit records.
- Open your Microsoft Excel spreadsheet
- Adjust Column A’s width to be a suitable width for all form columns. The form will use this column width as the default size for all form fields.
Set Column A to be wide
- Verify all your columns have a column heading.
- Highlight your data range including your column labels.
- From the Data tab, click the Form button.
Tip: You can click cell A1 and then Ctrl + Shift + → + ↓. to select your sheet range.
In earlier versions of Microsoft Excel, this function was on the Data menu. You may need to add the Form… command back to the Excel Ribbon or Quick Access toolbar. For this tutorial, I added the command to my Data tab, but some people prefer to create a new Forms tab.
If you have existing records, you will see a form for each record. The record count shows above the New button. If your spreadsheet is new, you’ll see a blank form with your labels.
You can use the Close button to shut down the form. To open again, click any cell within your defined range and click the Form button again on your ribbon or toolbar. You will see the form related to the clicked cell.
Improving Forms with Data Validation
Many seasoned Excel users like to use a feature called data validation. As the name suggests, this feature checks a cell’s value against a validation rule. There are many validation rules, such as time formats, number ranges, text length, etc. However, one item that can cause problems is when you use an Excel-dependent list.
For example, in the screen snap below, I have a validation rule that checks whether the cell’s entry is contained on a “state” list. You’ll also see a drop-down arrow to the cell’s right, which shows valid entries and a scroll bar.
While I can use data validation with my data form, I will not see the drop-down list. Instead of choosing from a pick list, I’ll need to type my entry. The issue here is that you might make errors. In the example below, you’ll see I’ve tried to add Disneyland as a state.
The validation logic is working, but the user might not know how to correct it. This is a case where you’d want to add a custom error message to help the user.
Finding and Filtering Data Records
Data forms do have rudimentary filtering features. These can be found by clicking the Criteria button on the right side. The form will clear any values and allow you to enter some values including wildcard characters such as ?, *, and ~. The button label also changes to Form so you can toggle back and forth.
In the example below, I’m looking to find any states that have more than 10 members in the US House. In the text box, I typed >10.
While this search works, it’s going to return one matching record at a time. To navigate to the others, you will use the Find Prev and Find Next buttons. Apart from number searches, you can also use wildcards as well. A question mark will represent any single character, and an asterisk will represent any number of characters.
Excel Data Form Limitations
As much as I like this form’s functionality, there are some limitations.
- If you get an error saying too many fields in the data form, you may have more than 32 columns. If so, try using a smaller range.
- If you get an error that states, “This can’t be applied to the selected range,” you probably clicked a cell outside the range.
- If your sheet has dependent lists, they won’t show in the data form. However, the logic will still work.
- You can’t print a data form record. Instead, you could use SnagIt or some other screen capture program.
- You can’t enter an Excel formula into a data form field.
If you need more than 32 fields, you can use a 3rd party utility. John Walkenbach had one which can be found using the Internet Archive.
The data entry process in Excel can be a tedious and frustrating experience for many users. If you are tired of spending hours entering information or dealing with errors, give an Excel data form a try.