In Chapter 17, you learned how to hide rows and columns in your worksheet from unauthorized glances. (Turn to the tip "Hide Rows or Columns for Security" if you'd like a refresher course.) Excel also lets you protect complete worksheets or an entire workbook from tampering by using a feature called password protection. When you guard worksheets or workbooks in this way, users can open the file, but they can't change the parts you have protected. If you want to share your workbooks with others, while protecting them from modification, this is the feature for you.
You can even require a password from users when they open a workbook. See "Requiring a Password for File Access."
To protect a worksheet in the workbook from modification, follow these steps:
The Protect Sheet dialog box contains a Password text box and three protection check boxes that are enabled by default. When the Contents check box is the only one selected, all the cells in the worksheet are protected, but any objects (such as clip art images) and worksheet scenarios will remain unprotected. To safeguard these items, be sure the Objects and Scenarios check boxes are also selected.
Use a Password (If Any) with Care
A password isn't required to protect worksheets. If you're afraid you'll forget the password, set worksheet protection without entering a password, and you'll preserve the worksheet from accidental entries and mistakes. (However, a renegade user could easily disable worksheet protection and then modify your document.)
Lock Specific Cells or Fields
If you want to let users modify some cells in your worksheet but not others, select the cells and clear the Locked option on the Protection tab of the Format Cells dialog box. This technique is useful if you have a field for comments or an area in the worksheet that is typically used for data entry. You must do this before you use the Protect Sheet command on the Protection submenu, or the Format Cells command will be unavailable to you. If this happens, turn off Protection, unlock the desired cells or fields, and then turn Protection back on.
To protect the structure of an entire workbook from modification (that is, to guard the names and the order of the worksheets), follow these steps:
The Protect Workbook dialog box contains a Password text box and two protection check boxes. When the Structure check box is selected, users can't insert, delete, hide, rename, copy, or move worksheets in the workbook, although they can modify data in the worksheets if worksheet protection is not set. When the Windows check box is selected, users can't resize the windows displaying the workbook.
Figure 18-10. The Protect Workbook dialog box.
If you work regularly in a multiuser environment, you might also enjoy the protection provided by the Protect And Share Workbook command on the Tools menu's Protection submenu. When you enable this toggle, it prevents users from modifying the revision history of a shared workbook.
If you're using Excel to track confidential information, you might want to limit access to your file by requiring a password to open it. This control goes further than protecting the content and structure of the workbook: it prevents anyone lacking an entry key from viewing your workbook at all.
Take care when using password protection. If you forget your password, you'll have no way to open the protected file.
To save a file that has password protection, follow these steps:
Figure 18-11. To protect your file from unauthorized access, type a password in the Save Options dialog box.
If you want to recommend, but not require that users open the file as a read-only document, select the Read-Only Recommended check box.