Protection Options

Excel offers a number of different protection options. For example, you can protect formulas from being overwritten or modified, protect a workbook's structure, and protect your VBA code.

Protecting Formulas from Being Overwritten

In many cases, you may want to protect your formulas from being overwritten or modified. To do so, you must unlock the cells that you will allow to be overwritten and then protect the sheet. First select the cells that may be overwritten and choose Home Cells Format Lock to unlock those cells. (The command toggles the Locked status.) Next, choose Home Cells Format Protect Sheet to show the Protect Sheet dialog. Here you can specify a password if desired.

Note 

By default, all cells are locked. Locking and unlocking cells has no effect, however, unless you have a protected worksheet.

Beginning with Excel 2002, Excel's protection options have become much more flexible. When you protect a worksheet, the Protect Sheet dialog box (see Figure 1-13) lets you choose which elements won't be protected. For example, you can allow users to sort data or use autofiltering on a protected sheet (tasks that weren't possible with earlier versions).

Figure 1-13: Choose which elements to protect in the Protect Sheet dialog box.

You can also hide your formulas so they won't appear in the Excel formula bar when the cell is activated. To do so, select the formula cells and press Ctrl+1 to display the Format Cells dialog box. Click the Protection tab and make sure that the Hidden check box is selected.

Protecting a Workbook's Structure

When you protect a workbook's structure, you can't add or delete sheets. Use the Review Changes Protect Workbook command to display the Protect Structure and Windows dialog box, as shown in Figure 1-14. Make sure that you enable the Structure check box. If you also mark the Windows check box, the window can't be moved or resized.

Figure 1-14: The Protect Structure and Windows dialog box.

Caution 

Keep in mind that Excel is not really a secure application. The protection features, even when used with a password, are intended to prevent casual users from accessing various components of your workbook. Anyone who really wants to defeat your protection can probably do so by using readily available password-cracking utilities.

Password-protecting a Workbook

In addition to protecting individual sheets and the structure of the workbook, you can require a password to open the workbook. To set a password, choose Office Prepare Encrypt Document to display the Encrypt Document dialog box (see Figure 1-15). In this dialog box, you can specify a password to open the workbook.

Figure 1-15: Use the Encrypt Document dialog box to specify a password for a workbook.