Microsoft Excel 2007 - Protecting Workbooks

Why Would You Need To Protect A Workbook?

There may be times when you want to keep information out of sight and safe from modification. Imagine, for example, a worksheet that contains static data that is used in calculations. In this situation it's advisable to hide and/or protect the data (and the formulas used, for that matter). It would not useful to the viewer to see this data, as it's only used behind the scenes, and any amendments made to it may corrupt the integrity of the calculations that use it. Here, it makes sense to both hide and protect. To this end, you can hide windows, individual worksheets or whole workbooks.

Hiding Workbooks

There may be times when you want to have several workbooks open and their information available so that Excel may use it. If you don't feel the need to have this information clutter up your screen you can hide the workbooks that you don't need by clicking View > Window > Hide in the appropriate workbook.

The workbook is hidden and its filename is removed from Switch Windows menu on the View tab. Once a workbook is hidden, the unhide button becomes available, and is displayed on all open workbooks. But suppose you have hidden several workbooks and you now click the Unhide button. How does Excel know which workbook to unhide? It doesn't, so it presents a dialog box for you to select the one you want to unhide. In this dialog box, all hidden workbooks are presented.

A word of warning! If you save a workbook whilst it is hidden, the next time you open it you won't see it. This can be very confusing, but is easy to rectify by clicking View > Window > Unhide (as we've already seen).

Protecting Worksheets

To prevent yourself or others from accidentally changing formulas or other data, you might want to protect your worksheet. To do this, click Review > Changes > Protect Sheet. In the Protect Sheet dialog box, tell Excel what kind of restrictions you want to put in place. For example, the default restrictions allow users to select locked and unlocked cells but prevent users from formatting cells, formatting columns etc. If the action appears in the dialog box, you can allow or restrict users' access to it.

Note that you can enter a password here to password protect your worksheet. Doing so will force the user to enter the password to unprotect the worksheet.

Unlocking Cells

Every cell has a locked attribute that determines whether you can update its contents when the worksheet is protected. When you protect a worksheet, all cells become locked. Should you need to make some cells available for update, you can do this by first changing the locked attribute. Select the cell or range of cells you need and then right click > Format Cells > Protection, and then uncheck the Locked check box. Click OK. Note that this has to be performed before you protect the worksheet.

On the Protection tab there is also a Hidden checkbox. If you check this box and then protect the worksheet, the contents of the selected cell still remain visible in the worksheet, but don't appear in the formula bar. Hiding cells this way will prevent users from seeing any formulas that you want to keep hidden. Again, this action needs to be performed before you protect the worksheet.

If you try and change the contents of a locked cell once the worksheet is protected, the following dialog box is displayed, preventing you from doing so.

To remove the worksheet's protection, click Review > Changes > Unprotect Sheet. If the worksheet was protected with a password you will need to enter that password now to unprotect it.

Protecting Workbooks

There are three ways you can protect a workbook:

• require entry of a password to open the workbook
• prevent structural changes to the workbook
• prevent the resizing and repositioning of windows within the workbook

Password Protection

To password protect a workbook, click the Office Button > Prepare > Encrypt Document. Enter the password in the Encrypt Document dialog box. As with all password protection, you will need to confirm the password. After the workbook has been saved, the password will need to be entered to open it again.

Prevent Structural Changes To The Workbook

Structural changes include the addition, deletion, movement, hiding or unhiding of worksheets within a workbook. To protect a workbook from these kinds of changes, click Review > Changes > Protect Workbook > Protect Structure and Windows. In the Protect Structure and Windows dialog box ensure that Structure is checked and assign a password if required. Similar steps need to be followed when unprotecting the workbook's structure: click Review > Changes > Protect Workbook > Protect Structure and Windows. You will need to enter a password if a password was previously assigned.

Protecting Workbook Windows

To protect the size and location of windows in a workbook, click Review > Changes > Protect Workbook. In the Protect Structure and Windows dialog box (see previous image), ensure that Windows is checked. To remove this protection, click Review > Changes > Protect Workbook > Protect Structure and Windows