Of all the work-related items I need to do, the one I hate is data entry. It’s mind numbing and given the chance, I’ll make mistakes. One way I found to make the process easier is to use Excel forms. These are especially beneficial when the records span across many columns.
I’ll admit entering data into Excel isn’t difficult. You type in one cell and move to the next. I had a project where the records spanned past the width of my monitor. Several of the cells contained a paragraph of pasted text. I wanted an input method that would allow me to focus on one organization. This would also make it easier to copy and paste paragraphs from the original PDF document.
The problem with using the default data entry method is it’s easy to lose sight of your target. You also have to navigate to the next row. A data form solves these issues.
What is a Excel Data Form?
A data form is a fancy phrase for a dialog box with the fields for one record. The form sits on top of your Excel spreadsheet and allows you to enter up to 32 fields per record. You might think of it as an on-screen form.
Your column headers become the form field labels. This is different than a custom Excel form a developer might create using VBA.
As you can see from the screenshot above, there are three main differences with the Excel forms.
- You’re only viewing one record.
- Your orientation is vertical, not horizontal.
- Some fields now have a shortcut key allowing you to jump within the form.
Using the form, I can enter data faster and when I Tab to the end of the form, I can hit Enter to start another record. I can also navigate through the records using the controls on the right side.
Switching to Excel Forms
The process for opening the data form depends on whether you’re using a new spreadsheet or one with existing data. The difference is the range you highlight.
To switch to the Data Form,
- Open your spreadsheet.
- Adjust the width in Column A to a suitable width for all columns. The form will use this width as the default size for all form fields.
- Add your column labels if this is a new sheet.
- Highlight your data range including column labels.
- From the Data tab, select Form.
You can click cell A1 and then Ctrl + Shift + + .
(Note: Older versions of Excel had this function on the Data menu. You may need to add the Form… command back on the Excel Ribbon.
If you have existing records, you will see a form for each record. If your spreadsheet is new, you’ll see a blank form with your labels.
Data Form Limitations
As much as I like this form functionality, there are some limitations.
- If you get an error saying too many fields you may have more than 32 columns. If so, try again selecting a smaller range.
- If your sheet has dependent lists, they won’t show in the data form. The logic will still work.
- You can’t print a data form record.
If you need more than 32 fields, you can use a 3rd party utility such as the free Enhanced Data Form from John Walkenbach.
While Excel forms may not make data entry fun, it does reduce the time it takes me to enter the data.