If you have ever used Excel to gather information from people in a pre-defined form setup, you know it can be frustrating when the responses contain added rows, columns, comments out of sight, etc. The purpose of taking the time to set up a form is so that you can get your data in a consistent format/layout.
It’s actually pretty easy to protect your worksheet from people who might want to get creative with your hard work.
Here’s the steps to lock all cells in your workbook, except for the few individual cells you want people to input information to.
- Click the box to the left of column A (in between column A and row 1). This will select all cells.
- Right click the same box – select “Format Cells” then click the “Protection” tab.
- Make sure the “Locked” check box is checked.
- Click “OK” These first few steps just made sure that all cells are locked. See steps 5-6 for unlocking individual cells.
- Right click just the cell(s) you wish to have editable by the end user (i.e. unlocked).
- Select “Format Cells” then click the “Protection” tab. Then un-check the “Locked” box. This will “unlock” the cells that you want the end user to be able to edit.
- In newer versions of Excel (2007+), click the “Review” tab, then click “Protect Sheet” under the “Changes” group.
In older versions of Excel (97-2003), this is found under the “Tools” menu, then “Protection”. - If you wish, enter a password in the prompt to be used to un-protect the sheet.
Note: If no password is entered, anyone can repeat step #7 to un-protect the sheet. - Under “Allow all users of this worksheet to:” un-check all boxes except for “Select unlocked cells” then click OK.
Users will now only be able to select and input into the cells that were unlocked in step #6. They won’t be able to insert or delete columns or rows, add comments, or any of the other actions listed in the Protect Sheet window.
It might also be a good idea to format your worksheet in such a way that the end user will know which cells can be (or should be) edited. For example, a different cell background or border color might be a good visual cue to someone that a cell is editable. Alternatively, you could type some generic text (i.e. “First Name”, “Last Name”, “Address”, etc.) into the cells, so the users will know what is expected of them in the cells.
Now what happens if you enter a password, want to go back and edit your worksheet, but forget what the password was? Well in most cases, you are stuck. You won’t be able to edit anything other than the unlocked cells. That is, of course, unless you know how to crack the password.