Designing a foolproof data entry form in Excel
Jan 16, 2001
Jeff Davis
© 2002 TechRepublic, Inc.
It's just too easy to kvetch about how incompetent some end users can be. This
week, I'd like to do something about it. Here's an Excel solution I've used to
help many end users become more confident and competent in doing something as
simple as filling out and printing an electronic form. I hope you can put this
tip to good use in your shops.
Get your Ground Zero here |
Subscribe to the View from Ground Zero TechMail, and you'll get a bonus of Jeff's picks for the best Web stuff—exclusively for TechMail subscribers. |
The problem
A small business owner recently called with a problem. All the client wanted was
for her sales reps to fill out a single form, on a routine basis, for the sake
of tracking internal accounting data. They tried writing the information by hand
on preprinted forms, but the penmanship was atrocious. They set up an electronic
form using Excel 97, but the sales reps kept overwriting formulas, changing text
labels, and overwriting their original "clean" copies of the form.
I decided all this client needed was some training in how to make an Excel-based
form as foolproof and as easy to use as possible. We set out to create a form
that only required typing and tabbing.
Summary of the technique |
Begin by setting up your labels—the strings that describe the data you want the user to enter. Then, select and unlock (unprotect) the cells in which the user will enter data. Turn off row and column headers, insert a nonprinting text box with your instructions for navigating in and saving the file, set the print range, and turn on worksheet protection. Finally, save the worksheet and activate the Read-Only Recommended option. |
The "typing and tabbing" solution
Of course, Excel offers a number of options for creating foolproof forms. This
eight-step approach doesn't require any programming and works the same way in
any version of Excel. (We captured our screen shots using Excel 97.) Here are
the details.
Figure A |
|
Start by entering labels for the data your users will enter. |
Download my foolproof Excel form |
Click here to download a working copy of this Excel form. |
Figure B |
|
Here's what our sheet looks like after we turned off a number of View options. |
Figure C |
|
Use a text box to provide helpful hints to your users. |
To keep that text box out of your printouts, you could exclude it from the print
range. However, there's a more elegant way: Make that text box "view
only." To do so, right-click on the text box's border and choose Format
Text Box. Select the Properties tab and deselect the option labeled Print
Object. Then the text will appear in the open worksheet, but it won't be part of
the print range.
Training
is the key
If a user clicks on or navigates to a protected cell, Excel will display a
message reminding the user about the protected status. Unfortunately, many users
will pick up the phone and call for help when they see that message.
To prevent that problem from occurring, teach your users up front that the
best way to use this form is to press [Tab] (or [Shift][Tab]) to navigate
between fields. Using the [Tab] key, the cursor will move to and from the
unprotected cells only! (That is, when you [Tab] through the last unprotected
cell, the cursor will bounce back up to the first one.)
Printing the completed form should be the easiest part of the training. Most
users know how to click the Standard toolbar's Print icon or press [Ctrl]P and
press [Enter].
My client loved that solution, and I hope your end users do, too. With any luck,
we won't get any calls from users who can't find the [Tab] key or the printer
icon!
Related reading: You can do even more to enhance your tab-and-type form
by adding validation rules and dropdown lists to the data entry cells. To find
out how, read "Ensure
accurate data entry in Excel by using Data Validation to create drop-down lists."
If your users are having problems entering rows of data, read "Speeding
up Excel data entry" to find out how the Data menu's Form option can
help.
Your take on the tip |
To comment on this tip, please post a note below or follow this link to write to Jeff. |
Copyright © 1999-2001 TechRepublic, Inc.
Created Date: 01/18/2002 Last Reviewed: 01/18/2002 Rev. Date: