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.
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.
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.
Lay out your data entry form. Start by typing and formatting a title and some labels for the data entry form, like the ones shown in Figure A. Notice that we "colorized" and adjusted the height of the rows between the major sections of this form and left several blank rows at the top of the sheet.
Unprotect the cells into which you want users to enter data. To do so, select the cell or range of cells, then open the Format menu and choose Cells (or right-click on the selection and choose Format Cells). Select the Protection tab and click the checkbox to deselect (uncheck) the Locked option. As you probably know, changing this setting doesn't affect the cells unless the sheet is protected, and we'll get to that in a moment. In our sample sheet, we unprotected cells B6-B8, B11-B12, and B15-B17.
Click here to download a working copy of this Excel form.
Make your worksheet look less like a worksheet and more like a "form." Start by turning off the formula bar, column and row borders, gridlines, and sheet tabs. Doing so eliminates many of the visual distractions that tempt and perplex so many users. Open the Tools menu and choose Options. Click the View tab. In the Show section, deselect the Formula Bar option. In the Window Options section, deselect the checkboxes for the Grid, Sheet Tabs, and Row & Column Headers options. When you click OK, your sheet will look like the one shown in Figure B.
Remind users to use [Tab] and [Shift][Tab]. The beauty of this approach is that the [Tab] and [Shift][Tab] keystrokes are all a user needs to navigate between fields. But users forget. So, put a text box at the top of the form that says, "Press [Tab] or [Shift][Tab] to move between fields." While you're at it, you might also mention how (or whether) the user should print or save the sheet after entering the data. To create that text box, click on the Drawing toolbar's Text Box tool and draw a box at the top of your sheet, then enter your text. Figure C shows what our sample text box looks like.
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.
Establish the appropriate print range. If you expect users to print your form, don't expect them to know how to select the correct print range. Before you distribute the worksheet, select and set the print area and set up your page formatting, including border rows and header and footer information.
Put the cursor in the first entry cell. Before you save the sheet for the last time, put the cursor in the first cell where the user will enter data. That way, there'll be zero need for users to navigate. They can open the file and then start typing and tabbing.
Turn on worksheet protection. You don't want users to be able to overwrite cells that contain your labels or formulas. So open the Tools menu, choose Protect, and select the Protect Sheet option. When you do, the Protect Sheet dialog will give you the chance to assign a password. After you turn on worksheet protection, Excel will allow changes only in the cells you previously formatted as unprotected.
Make the sheet read-only. Users are good at overwriting electronic forms. To prevent that problem, you have a couple of options. You can make the file itself read-only at the Windows level by setting the Read-Only flag in the File Properties dialog. Alternatively, you can use Excel's Read-Only Recommended option, although it offers a little less security because it only "recommends" that a user open a file as read-only. To use that option, open the File menu, choose Save As, and click the Options button. In the File Sharing Options section, activate the checkbox labeled Recommend Read-Only.
