The concept of “protection” gets a lot of attention in the Excel newsgroups and forums. It seems that many users want to learn how to protect their workbooks from being copied or modified. Excel has several protection-related features, and those features are covered in this chapter.
Excel’s protection-related features fall into three categories:
Worksheet protection: Protecting a worksheet from being modified, or restricting the modifications to certain users.
Workbook protection: Protecting a workbook from having sheets inserted or deleted, and also requiring the use of password in order to open the workbook.
VB protection: Using a password to prevent others from viewing or modifying your VBA code.
Caution
Before I discuss these features, you should understand the notion of security. Using a password to protect some aspect of your work doesn’t guarantee that it’s secure. Password-cracking utilities (and some simple tricks) have been around for a long time. Using passwords work in the vast majority of cases, but if someone is truly intent on getting to your data, he or she can usually find a way. If absolute security is critical, perhaps Excel isn’t the proper tool.
About Information Rights Management
Excel supports a feature known as Information Rights Management (IRM), which allows you to specify access permissions for workbooks. Using IRM may help prevent sensitive information from being printed, e-mailed, or copied by unauthorized people. When IRM is applied to a workbook, the permission information is stored in the document file itself.
To use IRM, you must install Microsoft’s Windows Rights Management Services (RMS)—an extra-cost product that isn’t included with Microsoft Office. You can access the IRM settings by choosing Office 
Prepare Restrict Permission.
IRM is not covered in this book. If your company uses RMS, consult your system administrator for more information about how this feature is used within your organization.
Excel users protect a worksheet for a variety of reasons. One reason is to prevent yourself or others from accidentally deleting formulas or other critical data. A common scenario is to protect a worksheet so that the data can be changed, but the formulas can’t be changed.
To protect a worksheet, activate the worksheet and choose Review Changes Protect Sheet. Excel displays the Protect Sheet dialog box shown in Figure 31.1. Note that providing a password is optional. If you enter a password, that password will be required to unprotect the worksheet. If you accept the default options in the Protect Sheet dialog box, none of the cells on the worksheet can be modified.
In many cases, you will want to allow some cells to be changed when the worksheet is protected. For example, your worksheet may have some input cells that are used by formula cells. In such a case, you would want the user to be able to change the input cells, but not the formula cells. Every cell has a Locked attribute, and that attribute determines whether the cell can be changed when the sheet is protected.
By default, all cells are locked. To change the locked attribute, select the cell or range and then use the Protection tab of the Format Cells dialog box (see Figure 31.2). To display this dialog box, right-click the cell or range and choose Format Cells from the shortcut menu (or press Ctrl+1). Remove the check mark from Locked and click OK.
Note
The Protection tab of the Format Cells dialog box has another attribute: Hidden. If checked, the contents of the cell don’t appear in the Formula bar when the sheet is protected. The cell isn’t hidden in the worksheet. You may want to set the Hidden attribute for formula cells to prevent users from seeing the formula when the cell is selected.
After you unlock the desired cells, choose Review Changes Protect Sheet to protect the sheet. After doing so, you can change the unlocked cells, but if you attempt to change a locked cell, Excel displays the dialog box shown in Figure 31.3.
To unprotect a protected sheet, choose Review Changes Unprotect Sheet. If the sheet is protected with a password, you’re prompted to enter the password.
The Protect Sheet dialog box has several options, which determine what the user can do when the worksheet is protected.
Select locked cells: If checked, the user can select locked cells using the mouse or the keyboard. This setting is checked, by default.
Select unlocked cells: If checked, the user can select unlocked cells using the mouse or the keyboard. This setting is checked, by default.
Format cells: If checked, the user can apply formatting to locked cells.
Format columns: If checked, the user can hide or change the width of columns.
Format rows: If checked, the user can hide or change the height of rows.
Insert columns: If checked, the user can insert new columns.
Insert rows: If checked, the user can insert new rows.
Insert hyperlinks: If checked, the user can insert hyperlinks (even in locked cells).
Delete columns: If checked, the user can delete columns.
Delete rows: If checked, the user can delete rows.
Sort: If checked, the user can sort data in a range (as long as the range doesn’t contain any locked cells).
Use AutoFilter: If checked, the user can use existing autofiltering.
Use PivotTable reports: If checked, the user can change the layout of pivot tables or create new pivot tables.
Edit objects: If checked, the user can make changes to objects (such as Shapes) and charts, as well as insert or delete comments.
Edit scenarios: If checked, the user can use scenarios (see Chapter 36).
Excel also offers the ability to assign user-level permissions to different areas on a protected worksheet. You can specify which users can edit a particular range while the worksheet is protected. As an option, you can require a password to make changes.
This feature is rarely used, and the setup procedure is rather complicated. But if you need this level of protection, setting it up is worth the effort to get it.
Start by unprotecting the worksheet if it’s protected. Then choose Review Changes Allow Users To Edit Ranges, which displays the dialog box shown in Figure 31.4. Then follow the prompts in the series of dialog boxes that follow. Make sure that you protect the sheet as the final step.
Excel provides three ways to protect a workbook:
Require a password to open the workbook
Prevent users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets
Prevent users from changing the size or position of windows
I discuss each of these methods in the sections that follow.
Excel lets you save a workbook with a password. After doing so, whoever tries to open the workbook must enter the password.
To add a password to a workbook:
Choose Office
Prepare Encrypt Document. Excel displays the Encrypt Document dialog box shown in Figure 31.5.Type a password and click OK.
Type the password again and click OK.
Save the workbook.
Note
You need to perform these steps only one time. You don’t need to specify the password every time you resave the workbook.
To remove a password from a workbook, repeat the same procedure. In Step 2, however, delete the existing password symbols from the Encrypt Document dialog box, click OK, and save your workbook.
Figure 31.6 shows the Password dialog box that appears when you try to open a file saved with a password.
Excel provides another way to add a password to a document:
Choose Office
Save As.In the Save As dialog box, click the Tools button and choose General Options. Excel displays the General Options dialog box.
In the General Options dialog box, enter a password in the Password to Open field.
Click OK. You’re asked to re-enter the password before you return to the Save As dialog box.
In the Save As dialog box, make sure that the filename, location, and type are correct and then click Save.
Note
The General Options dialog box has another password field: Password to Modify. If you specify a password for this field, the file opens in read-only mode (it can’t be saved under the same name) unless the user knows the password. If you use the Read-Only Recommended check box without a password, Excel suggests that the file be opened in read-only mode, but the user can override this suggestion.
To prevent others (or yourself) from performing certain actions in a workbook, you can protect the workbook’s structure. When a workbook’s structure is protected, the user may not
Add a sheet
Delete a sheet
Hide a sheet
Unhide a sheet
Rename a sheet
Move a sheet
To protect a worksheet’s structure:
Choose Review
Changes Protect Workbook to display the Protect Workbook dialog box (see Figure 31.7).In the Protect Workbook dialog box, place a check mark next to Structure.
Enter a password, if desired.
Click OK.
To unprotect the workbook’s structure, choose Review Changes Unprotect Workbook. If the workbook’s structure was protected with a password, you are prompted to enter the password.
To prevent others (or yourself) from changing the size or position of a workbook’s windows, you can protect the workbook’s windows:
Choose Review
Changes Protect Workbook.In the Protect Workbook dialog box, place a check mark next to Windows.
Enter a password, if desired.
Click OK.
When a workbook’s windows are protected, the user can’t change anything related to the window size or position. For example, if the workbook window is maximized when the windows are protected, the user cannot unmaximize the window. The windows can, however, be zoomed.
To unprotect the workbook’s windows, choose Review Changes Unprotect Workbook. If the workbook’s windows were protected with a password, you are prompted to enter the password.
If your workbook contains any VBA macros, you may want to protect the VB Project to prevent others from viewing or modifying your macros. Another reason to protect a VB Project is to prevent its components from being expanded in the VB Editor Project window (which can avoid clutter while you’re working on other VB project). To protect a VB Project:
Activate the VB Editor.
Select your project in the Projects window.
Choose Tools - xxxx Properties (where xxxx corresponds to your Project name). Excel displays the Project Properties dialog box.
In the Project Properties dialog box, click the Protection tab (see Figure 31.8).
Place a check mark next to Lock project for viewing.
Enter a password (twice).
Click OK and then save your file. When the file is closed and then re-opened, a password will be required to view or modify the VBA code.
Cross-Ref
Part VI discusses VBA macros.
This section covers additional topics related to protecting and distributing your work.
You can download a free Office 2007 add-in that enables you to save a workbook as a PDF file. Because of a legal dispute with Adobe Systems, Microsoft is not able to ship this add-in with Office 2007. You can download the add-in from:
http://office.microsoft.com/downloads
The PDF (Portable Document Format) file format is widely used as a way to present information in a read-only manner, with precise control over the layout. Software to display PDF files is available from a number of sources. Excel (with the assistance of the add-in) can create PDF files, but it cannot open them.
After installing the add-in, you can save your workbook in PDF or XPS format by choosing Office Save As PDF Or XPS. Excel displays its Publish As PDF Or XPS dialog box, in which you can specify a filename and location and set some other options.
Excel lets you mark a document as “final.” This action makes two changes to the workbook:
It makes the workbook read-only so that the file can’t be saved using the same name.
It makes the workbook view-only so that nothing may be changed. When you open a finalized document, the status bar displays an additional icon. You’ll find that most of the Ribbon commands are grayed out.
To finalize a workbook, choose Office Prepare Mark As Final. Excel displays a dialog box so that you can confirm your choice.
Caution
Marking a document as final is not a security measure. Anyone who opens the workbook can choose Office Prepare Mark As Final to cancel the mark as final designation. After the user selects that command, the workbook is no longer read-only or view-only. Therefore, this method doesn’t guarantee that others will not change the workbook.
If you plan to distribute a workbook to others, you may want to have Excel check the file for hidden data and personal information. This tool can locate hidden information about you, your organization, or about the workbook that you may not want to share with others.
To do so, choose Office Prepare Inspect Document. You see the dialog box shown in Figure 31.9. Click Inspect, and Excel displays the results of the inspection and gives you the opportunity to remove the items it finds.
Caution
If Excel identifies items in the Document Inspector, it doesn’t necessarily mean that they should be removed. In other words, you should not blindly use the Remove All buttons to remove the items that Excel locates. For example, you may have a hidden sheet that serves a critical purpose. Excel will identify that hidden sheet and make it very easy for you to delete it. To be on the safe side, always make a backup copy of your workbook before running the Document Inspector.
Excel lets you add a digital signature to a workbook. Using a digital signature is somewhat analogous to signing a paper document. A digital signature helps to assure the authenticity of the workbook and also ensures that the content hasn’t been modified since it was signed.
After you sign a workbook, the signature is valid until you make changes and resave the file.
In order to digitally sign a workbook, you must obtain a certificate from a certified authority who is able to verify the authenticity of your signature. Prices vary, depending on the certificate granting company.
Another option is to create your own digital ID, but others will not be able to verify the authenticity. Creating your own digital ID is useful if you want to ensure that no one has tampered with one of your signed workbooks.
Excel 2007 supports two types of digital signatures: a visible signature and an invisible signature.
To add a visible digital signature, choose Insert Text Signature Line Microsoft Office Signature Line. Excel displays its Signature Setup dialog box, and you’re prompted for the information for the signature. After you add the signature box, double-click it to display the Sign dialog box, where you actually sign the document either by typing your name or uploading a scanned image of your signature.
Figure 31.10 shows a document with a visible digital signature.
To add an invisible digital signature, choose Office Prepare Add a Digital Signature.