Excel Tip: Forms & Protection

Sometimes we need to create form-like sheets in a workbook, for example where user input is wanted. Presenting a plain grid is bland. Usually cell colouring results in a more attractive form, e.g.

Here is an approach to creating a sheet that behaves like a form, using cell protection:

1. Create a form using background colours in prompt cells
2. Click on Ribbon, View tab: uncheck …

• Gridlines
• Formula Bar
• Headings

1. Select all cells by clicking on the left and above cell A1 (there’s a little shaded triangle in that square)
2. Right click on any cell (while they’re all still all selected), and choose “Format Cells”

• In the “Format Cells” dialog, click on “Protection” tab,
• Ensure “Locked” checkbox is checked
• Click OK

1. Now, every cell in this sheet is locked, but nothing changes until sheet protection is applied
2. Before protecting, select the user input cells (white cells in the example above)
3. Right click and choose Protection. This time, ensure that “Locked” is unchecked, so users can enter data
4. Protect the sheet:

• Click Review Tab, Protect Sheet
• Uncheck “Select locked cells”
• Enter a password, click OK
• Confirm password, click OK

Test the form. You should find that tab/shift tab or arrow keys will move to next/previous data entry cell. Clicking on prompt (or any other) cells has no effect.

There’s a downside: without a password, it will be impossible to make changes going forward. So, note it where it can be found.