CHAPTER

Protecting Your Work

637

Part IV: Using Advanced Excel Features

About Information Rights Management

Excel supports an Information Rights Management (IRM) feature, 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 the Microsoft Windows Rights Management Services (RMS) — an extracost product that isn’t included with Microsoft Office. You can access the IRM settings by choosing File Info Protect Workbooks Restrict Permission By People.

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.

Protecting a Worksheet

You may want to 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 (and if you haven’t unlocked any cells), none of the cells on the worksheet can be modified.

To unprotect a protected sheet, choose Review Changes Unprotect Sheet. If the sheet was protected with a password, you’re prompted to enter that password.

FIGURE 31.1

Use the Protect Sheet dialog box to protect a worksheet.

638

Chapter 31: Protecting Your Work

Unlocking cells

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, right-click, and choose Format Cells from the shortcut menu (or press Ctrl+1). The Protection tab of the Format Cells dialog box opens (see Figure 31.2); clear the Locked check box and then click OK.

FIGURE 31.2

Use the Protection tab in the Format Cells dialog box to change the Locked attribute of a cell or range.

Note

The Protection tab of the Format Cells dialog box has another attribute: Hidden. If this check box is selected, 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. n

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 warning alert shown in Figure 31.3.

639

Part IV: Using Advanced Excel Features

FIGURE 31.3

Excel warns you if you attempt to change a locked cell.

Sheet protection options

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 enabled by default.

•Select Unlocked Cells: If checked, the user can select unlocked cells using the mouse or the keyboard. This setting is enabled 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).

Tip

When the worksheet is protected and the Select Unlocked Cells option is set, pressing Tab moves to the next unlocked cell, making data entry much easier. n

640