This Excel Tip shows four (4) easy steps to create a form in Excel. Forms in Excel can be overwhelming; but when made simple, just about anyone can create them. Excel forms can simplify Timesheets, Expense forms, and more.
There are a few ways to create forms in Excel. Here, I will show you (in my opinion) the easiest.
An easy example to use is a Timesheet or Expense form:
1) First, type in the information as you would like it captured. You may type in the line descriptions or categories for the Expenses and place colored cells for the areas in which you want users to type in their actual expense amount. Once the form is created, you will "unlock" the cells that you wish the user to input data.
2) To do this, click on Format | Cells and click on the "Protection" tab. On this tab you will notice that the cells are "locked" by default. Simply uncheck the box and click OK.
3) Next, click on Tools | Protection and choose "Protect Sheet" (or "Protect Workbook" if you are working with more than one Sheet). This will open a separate window asking you what you would like to protect. In the "Allow all users of this worksheet to" area, make sure the only item checked is "Select unlocked cells."
NOTE: When you are Protecting a Sheet or Workbook, Excel will ask you for a password. Make sure you choose a password if you do not want users to edit the form. Excel does NOT require you to use a password.
4) Finally, click File | Save As and choose "Excel Template" from the "Save as Type" drop down. This will save your file in the default folder.
NOTE: If you are working on a network, be sure and get with your administrator for the exact location to save this file.
Now that you have saved your form, users will simply click File | New and choose "on my computer" for the location of the template. Once the users double-click to open the template, they will only be able to type information in the unlocked cells.
I hope this Excel Tip helps you easily create forms in Excel.