How to Create Fill-in Forms using Excel

On a recent shopping trip to purchase a wedding present, I came to a standstill in the kitchen appliance aisle. I was stunned by the number of “one trick ponies” beckoning for my attention – waffle makers, Mickey Mouse shaped waffle makers, rice cookers, bread makers, silver dollar pancake makers, panini makers, sandwich grills. I wondered if it wouldn’t be more space and cost efficient to just use a good set of pots and pans.

So it is with software designed to do a singular task. I am sometimes infatuated by the latest marketing claims that promise to make quick order of a particular function. With a little more thought, however, I am usually swayed back to my tried and true tools. One example is making fill-in forms, several software packages may be purchased for this task. However, creating fill-in forms in Excel takes just a few steps.

There are two settings that must be coordinated, both found in the Format dropdown in the Cells group of the Ribbon. The default settings for the two settings are set as Protect Sheet set to OFF and Lock Cells set to ON. We’ll double check the Lock Cells as a first step.

1. Press CTRL-A to select the whole worksheet. Click on the drop-down arrow on Format in the Cells group. Check that the Lock Cells is ON, indicated by the shaded square around the lock icon. If in doubt, click the lock and the shaded background will toggle on and off.

Forms_Excel_1

2. Create the form with appropriate labels and open cells in which the users will input their information. For example, Cell A2 below shows the label “Name.” Cells B2 and C2 are blank and Merged as one cell for entry of the user’s name.

Forms_Excel_3

3. Select each of the input cells, in the above case, B2, C2, E2, and B3, C3, D3, E3. Cells which are non-contiguous may be selected by holding down the CTRL key while clicking on each cell.

4. Click on the drop-down arrow on Format in the Cells group. In the Protection section, click on Lock Cell. This will toggle OFF the lock for only the selected cells.

Forms_Excel_5

5. Click Protect Sheet right above the Lock Cell option. The dialog box provides several options to enable a customized user experience. Protecting the spreadsheet with a password will prevent the user from making any changes to the form without entering the password. Checking the box “Select unlocked cells” and unchecking “Select locked cells” will allow the user to conveniently tab through all of the input cells and skip over the label cells. Forms_Excel_6

6. Finally, save the file as an Excel Template so that each time the file is opened, a new copy is opened, leaving the original intact for future uses.

To see this form in action, download it here: Fill-in Form in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *