Turning on worksheet protection

When all cell ranges where you want to allow editing are unlocked, you're ready to turn on protection. To do this, you choose Tools → Protection → Protect Sheet to open the Protect Sheet dialog box.

When you first open this dialog box, the following check boxes are selected:

• The Protect Worksheet and Contents of Locked Cells check box at the very top
• The Select Locked Cells and Select Unlocked Cells check boxes in the Allow All Users of This Worksheet To list box

All the other check box options (including several that are not visible without scrolling up the Allow All Users of This Worksheet To list box) are unselected. This means that if you click OK at this point, the only things that you'll be permitted to do in the worksheet are to edit unlocked cells and to select cell ranges of any type (both locked and unlocked alike).

If you really want to keep other users out of all the locked cells in a worksheet, clear the Select Locked Cells check box in the Allow All Users of This Worksheet To list box. That way, your users are restricted to just those unlocked ranges where you permit data input and contents editing.

Don't ever clear the Select Unlocked Cells check box and the Select Locked Cells check box at the same time. Doing so renders the worksheet completely useless by removing the cell pointer from the worksheet and making it impossible for the user to edit even the unlocked cells of the spreadsheet.

In addition to enabling users to select locked and unlocked cells in the worksheet, you can enable the following actions in the protected worksheet by selecting their check boxes in the Allow All Users of This Worksheet To list box:

Format Cells:
Enables the formatting of cells (with the exception of changing the locked and hidden status on the Protection tab of the Format Cells dialog box).

Format Columns:
Enables formatting so that users can modify the column widths and hide and unhide columns.

Format Rows:
Enables formatting so that users can modify the row heights and hide and unhide rows.

Insert Columns:
Enables the insertion of new columns in the worksheet.

Insert Rows:
Enables the insertion of new rows in the worksheet.

Insert Hyperlinks:
Enables the insertion of new hyperlinks to other documents, both local and on the Web.

Delete Columns:
Enables the deletion of columns in the worksheet.

Delete Rows:
Enables the deletion of rows in the worksheet.

Sort:
Enables the sorting of data in unlocked cells in the worksheet.

Use AutoFilter:
Enables the filtering of data in the worksheet.

Use Pivot Table Reports:
Enables the manipulation of pivot tables in the worksheet.

Edit Objects:
Enables the editing of graphic objects, such as text boxes, embedded images, and the like, in the worksheet.

Edit Scenarios:
Enables the editing of what-if scenarios, including modifying and deleting them.

In addition to enabling particular actions in the protected worksheet, you can also assign a password that's required in order to remove the protections from the protected worksheet. When entering a password in the Password to Unprotect Sheet text box of the Protect Sheet dialog box, you observe the same guidelines as when assigning a password to open or to modify the workbook (a maximum of 255 characters that can consist of a combination of letters, numbers, and spaces, with the letters being case sensitive).

When you enter a password in the Password to Unprotect Sheet text box and then click OK, Excel displays the Confirm Password dialog box. Here, you must accurately reproduce the password you just entered (including upper- and lowercase letters) before Excel turns on the sheet protection and assigns the password to its removal.

If you don't assign a password to unprotect the sheet, any user with a modicum of Excel knowledge can turn off the worksheet protection and start making all manner of changes to its contents, including wreaking havoc on its formulas. It makes little sense to turn on the protection in a worksheet if you're going to permit anybody to turn it off by simply choosing the Tools → Protection → Unprotect Sheet command.