Protecting Cells, Sheets, Workbooks, And Files

Excel offers many ways to protect your data. But understanding the differences between the methods and the limitations of each of them can be daunting. Here's a quick review of four protection options in Excel 97 and later, plus explanations of how to use them.

Note: These protection features aren't foolproof. Password-cracking utilities exist, and anyone who wants to defeat your protection badly enough probably can.

Cell protection

Every cell has two key properties: locked and hidden. A locked cell can't be changed, and the contents of a hidden cell don't appear in the formula bar when the cell is selected. By default, every cell is locked and not hidden. Locked and hidden attributes have no effect unless the worksheet is protected.

To change these attributes, select the appropriate cell or a range and then choose Format, Cells. In the Format Cells dialog box, click the Protection tab and select Locked or Hidden. Unlock cells that accept user input, and lock formula cells and other cells that should stay unchanged (such as titles). To prevent others from seeing your formulas, lock and hide the formula cells--the results of the formulas will be visible, but the formulas will not.

To protect a sheet, choose Tools, Protection, Protect Sheet to bring up the Protect Sheet dialog, and make sure Contents is checked. You can enter a password to prevent others from unprotecting the sheet. Locked cells in a protected sheet cannot be edited, and other worksheet changes are disabled. For example, no one can insert rows or columns, change column width, or create embedded charts.

Note: Excel 2002 offers additional options regarding what the user can and cannot do when a sheet is protected. For example, you can protect a sheet and still allow sorting and autofiltering.

Workbook window protection

The Tools, Protection, Protect Workbook command enables you to protect a workbook so no one can add or delete sheets or resize or move the workbook window.

Workbook file protection

Here's how to save a workbook in such a way that you must supply a password to open or modify it:

• In Excel 97, select File, Save As to display the Save As dialog box; then click Options in that box to call up the Save Options dialog.
• In Excel 2000 and later, select Tools, General Options when the Save As dialog box appears. If you enter a password in the "Password to open" field, the user must provide the correct password to open the file. If you enter a password in the "Password to modify" field, the user must give the right password to make changes to the file. Without the correct password, the file opens in read-only mode.

Note: Excel 2002 and later also lets you specify the type of encryption, if any, that will be used. Access this feature by clicking the Advanced button in the Save Options dialog box.

VBA project protection

If your workbook contains VBA macros, you can use these to prohibit others from viewing or modifying them. Press Alt-F11 to activate the VB Editor window, and select your project from the Project window. Choose Tools, VBA Project Properties. Click the Protection tab, place a check mark next to "Lock project for viewing," and enter the password twice. Click OK and save your workbook.