How to Use Excel to Make Forms for Text Entry

Excel may be billed as a spreadsheet that you use for financial analysis and list management, but it's much more versatile than that. The row-and-column nature of a spreadsheet makes it an excellent tool for creating page layouts for documents like forms. The key is to make the page look like a form and to prevent the user from modifying anything other than the areas that you want them to fill in. Excel's cell formatting, merge cells and protect sheet features all make this easy.

Preparing the Form

Open Excel and create a new document.

Press "Ctrl-A" to select the entire document and right-click on any of the row headings on the left side of the screen. Select "Row Height" and enter a height -- 15 is usually Excel's default. While you can change this in the future, manually forcing a height will prevent cells from automatically resizing themselves and damaging your spreadsheet's layout.

Press "Ctrl-A" to select the entire document if it isn't still selected, click the Fill Color option in the Font section of the Home tab and select a white background. This will get rid of the gridlines and make the spreadsheet look like a blank page. If you find it hard to work on a spreadsheet without gridlines, you can delay this step, but you will need to do it before you add any additional cell shading.

Click "View" and then "Page Break Preview." Click "OK" in the dialog box and then click "Normal." This will put dotted lines on your screen so that you can see where the page ends and use them to see the boundaries of your form.

Creating the Form

Lay out your form by putting text on screen and adding cell borders to create areas. Remember to make areas where you want your users to enter text large enough to hold everything they might reasonably type. If you want, have them span multiple cells so that you can have boxes that are wide and tall.

Click "File" then "Save" to save your work; then click "Home" to return to the Home tab.

Highlight all of the cells in a particular text entry area. Click "Merge & Center" in the Alignment tab and then, while they're still highlighted, click the "Wrap Text" button. This will allow the user to enter text and have it wrap to fill the cell.

Click the cell color pull-down menu in the Font section of the Format tab to give your text entry areas a colored background, if you want. If you unselected the text entry area, reselect it before doing this. You may also want to select a font that will fit in the cell by clicking the font name and font size pull-down menus. For example, 8 point Arial is a little bit small but still easy to read.

Repeat steps 3 and 4 until you have formatted every text entry area. Save your work when you are done.

Protecting the Form

Press "Ctrl-A" to select every cell in the workbook. Right-click the cells and select "Format Cells" from the menu. Click the "Protection" tab, check the box next to "Locked" if it isn't already checked and then click "OK." By default this sets your form to protect every cell.

Select a text entry area, right-click, select "Format Cells," click the "Protection" tab and uncheck the box next to "Locked." This will unlock only the text entry area. If you don't want to do this one cell at a time, hold down the Control key and left-click each field to select it; then right-click and unprotect them all.

Click the "Review" tab and then click "Protect Sheet." Enter an unlock password in the field, click "OK," reenter your password and click "OK" one more time. This will lock your entire spreadsheet except for the text entry areas.

Save your finished form.