Description of options that help you protect your worksheets in Excel 2003 and Excel 2002

This article describes the worksheet protection options that are available in Microsoft Excel 2002 and Microsoft Office Excel 2003.

Menus and Toolbars

On the Tools menu, under the Protection option, you find the Allow Users to Edit Ranges dialog box. In the Allow Users to Edit Ranges dialog box, you can list named ranges and their corresponding cells that specific users, computers, or groups have been designated for special access by using an assigned password. Through this dialog box, you can also create new ranges, modify existing ranges, or delete ranges that have been granted permission for editing.

Note The Allow Users to Edit Ranges dialog box is not available if the worksheet is protected.

Excel 2003 has a toolbar that contains all the commands that are available on the Protection submenu, and also the Lock Cell command. You can make the Protection toolbar appear. To do so, click to select the Protection check box under Toolbars on the View menu.

Adding User or Group Permissions

Excel workbooks can use edit permissions based on users or groups instead of needing passwords to restrict access to workbooks.

The Allow Users to Edit Ranges dialog box includes the Specify Who May Edit the Range Without a Password section that contains a Permissions button. The Permissions button is also available in the New Range dialog box and the Modify Range dialog box.

When you click the Permissions button, the Permissions for Range Name dialog box appears.

The Permissions for Range Name dialog box contains the following elements:

• The Group or user names list (Microsoft Windows XP) or the Name list (Microsoft Windows 2000) that contains some users and groups. You add names to this list so that you can select to allow or to deny permissions to edit this range as appropriate.
• The Add button displays the Select Users, Computers, or Groups dialog box. You can select the users and groups that you want to give permissions to, and then add them to the Name list in the Permissions for Range Name dialog box.
  
  Note For more information about users and groups, or the Select User, Computers, or Groups dialog box, see the Windows 2000 or Windows XP Help files.
• The Remove button removes the selected item in the list of names. There is no confirmation to undo the action. Instead, you must click Cancel in the Permissions for Range Name dialog box.
• The Permissions options box contains check boxes to either allow or to deny the selected user or group permission to edit the range without a password.

When you click to select the Allow check box, the specified user can edit the range without using a password if that user is logged on to the domain that is validated by Windows 2000 or Windows XP.

When you click to select the Deny check box, the specified user must type the correct password to edit the range. When the Deny check box is selected, a message appears when you click OK or when you click Apply to report that Deny permissions take priority over Allow permissions. The message gives the user the option to continue or not. If your user has overlapping permissions of both Deny and Allow, the user must type a password to edit the range.

Important User permissions are overridden if a password is not specified for the range, and then the range can be edited by anyone.

You must enable worksheet protection to make the ranges to edit with a password take effect. If you do not enable worksheet protection, anyone can edit the worksheet.

If the cells Locked property is not enabled, this state takes precedence over any ranges that are specified and therefore anyone can edit any cell that is unlocked.

Worksheet Protection Options

The Protect Sheet dialog box enables you to select protection for objects and scenarios, as did earlier versions of Excel. However, in Excel 2002 and in Excel 2003, cell contents are always protected when the worksheet is protected.

Tip You can use Microsoft Visual Basic for Applications (VBA) to protect a worksheet for objects and for scenarios, but not for contents.

Cell contents can be unlocked on a user or a password level, but the worksheet protection options can be changed only with the worksheet protection password. If a cell is locked in the Format Cells dialog box, it is always considered locked for the worksheet protection. If a user has permission to edit a range that includes a locked cell, the cell is still considered locked because the range permissions override the locked cell format but do not change it. Users who have permission to edit a range of cells are still restricted by the worksheet protection options. For example, if the Format cells check box is cleared, no one can format any cells in the protected worksheet even if they have the password or the permissions to edit the cells.

The Protect Sheet dialog box contains the following check boxes:

• By default the Select locked cells check box is selected. This check box enables users to select cells with the Locked check box selected in the Format Cells dialog box. When the Select locked cells check box is selected, the Select unlocked cells check box is automatically selected.
• By default the Select unlocked cells check box is selected. This check box enables users to select cells with the Locked check box cleared in the Format Cells dialog box. When the Select unlocked cells check box is cleared, the Select locked cells check box is automatically cleared. If there are no unlocked cells on a protected sheet and this check box is not selected, users cannot select any cells on the worksheet.
• The Format cells check box enables Conditional Formatting and enables every control in the Format Cells dialog box for every cell on the worksheet, including locked cells, with the following exceptions:
  • The Protection tab is always hidden on a protected sheet.
  • On the Alignment tab, the Merge cells check box is always unavailable.
  Note You cannot permit formatting of unlocked cells only.
• The Format columns check box enables every item in the Column submenu of the Format menu.
• The Format rows check box enables every item in the Row submenu of the Format menu.
• The Insert columns check box permits a user to insert columns anywhere on the worksheet (subject to ordinary conditions).
  
  Note If the Delete columns check box is also cleared, the user can insert columns that cannot be deleted except by using the Undo command.
• The Insert rows check box permits a user to insert rows anywhere on the worksheet (subject to ordinary conditions).
  
  Note If the Delete rows check box is also cleared, the user can insert rows that cannot be deleted except by using the Undo command.
• The Insert hyperlinks check box makes the Hyperlink command on the Insert menu available when the current range of selected cells is made up of only unlocked cells.
• The Delete columns check box enables a user to delete any column that does not contain a locked cell. If a user tries to delete a column that contains a locked cell, a message appears that states that locked cells in a protected worksheet cannot be deleted.
• The Delete rows check box enables a user to delete any row that does not contain a locked cell. If a user tries to delete a row that contains a locked cell, a message appears that states that locked cells in a protected worksheet cannot be deleted.
• The Sort check box enables the Sort dialog box on the Data menu. Users can sort only if the range to be sorted does not contain locked cells. If a user tries to sort a range that contains locked cells, a message appears that states that the sheet must be unprotected before the data can be sorted.
• The Use AutoFilter check box permits a user to change the filter criteria on an existing AutoFilter. This check box does not allow a user to add or to remove an AutoFilter. The AutoFilter must exist before you protect the sheet.
• The Use PivotTable reports check box enables a user to make changes to an existing PivotTable. This check box does not enable the user to add or to remove a PivotTable. The PivotTable must exist before you protect the sheet.
• The Edit objects check box removes any protection from an object except any protection properties that are set for the object.
• The Edit scenarios check box removes protection from scenarios.

Backward Compatibility

The new protection features are not recognized in versions of Excel that are earlier than Excel 2002. If you save the file in a Microsoft Excel 95 (or earlier) file format, you lose the new protection options and the edit ranges. The earlier protection options are saved correctly in earlier Excel file formats that support the protection options. These options include the sheet-protection password, and the Edit Object item, the Edit Scenario item, and the Protect Contents item (not available in the user interface of Excel 2002 or Excel 2003). Protection options and password edit ranges are preserved when a file is saved in the Microsoft Excel 97 file format and the Microsoft Excel 2000 file format. The use of future record types allows you to save the file in Excel 97 or in Excel 2000 and still maintain the new features when you open the file back into Excel 2002 or into Excel 2003.

When you open an Excel 2002 or an Excel 2003-protected sheet in Excel 2000 or earlier, the Excel 2003 protection functionality reverts back to the level of protection that was available in that version of Excel. Generally, the new protection features in Excel 2002 and in Excel 2003 are exceptions to the default level of protection. In other words, if you use Excel 2000 to open a workbook that was created in Excel 2003, you are not allowed to make changes that are protected in Excel 2003. Protection options (for example, enabling Insert columns on a protected sheet) do not apply to earlier versions. Everything that is disabled on a protected worksheet in Excel 2000 and earlier remains protected, even if the workbook was created in Excel 2003.

Password edit ranges have no meaning in versions of Excel earlier than Excel 2002. Users and passwords for ranges are not validated, and therefore protection is not compromised. The ranges remain locked. The cells cannot be edited as long as the worksheet is protected and the cells have the Locked format set. If you neglect to perform either of these actions, the cells can be edited in Excel 2002 or in Excel 2003 without a password.

The Excel 95, Excel 97, and Excel 2000 VBA properties work as if they were set by using VBA if the corresponding options are set through the Excel 2003 Protect Sheet dialog box.

When you open a workbook that is protected in a version of Excel before Excel 2002, the workbook opens in Excel 2003 with the protection level that matches the options that are set in the earlier version.

Using Enhanced Encryption

In Excel 2003, you have the encryption schemes that are available in earlier versions of Excel, and the encryption schemes from Microsoft Internet Explorer's CryptoAPI. Internet Explorer's CryptoAPI was added to Excel 2002.

To set the encryption type, follow these steps:

1. On the Tools menu, click Options.
2. On the Security tab, click Advanced.
3. Select the encryption type, and then select other options that you want.
4. Click OK, and then click OK.

You can also access the Encryption Type dialog box in the Save As dialog box. To do so, follow these steps:

1. On the File menu, click Save As.
2. In the Save As dialog box, click Tools on the standard toolbar.
3. Click General Options.
4. In the Save Options dialog box, click Advanced.
5. Select the encryption type, and then select other options that you want.
6. Click OK, and then click OK.

Use caution when you select the encryption type that you want to protect a workbook with because it may not be available on all systems. The Microsoft Office 97 and Microsoft Office 2000-compatible encryption type is the default because it is the most likely to be available. If you try to open a workbook that is encrypted by an encryption type that is not installed, you may receive a message that states that Excel cannot open the workbook.

Workbooks that are encrypted with the newer encryption types cannot be opened in versions of Excel before Excel 2002. Always use the Office 97 and Office 2000-compatible encryption type if you have to open these workbooks in versions of Excel before Excel 2002. When you try to open a workbook that is protected with a newer encryption type in a version of Excel before Excel 2002, you are prompted to type the password and, even if you type the correct password, Excel cannot recognize the password and alerts you that the password is incorrect.

The workbook can be shared in the same manner as in earlier versions except that you must have Excel 2002 or Excel 2003 and the appropriate encryption provider installed to open the workbook when you use an enhanced encryption scheme. If you use an enhanced encryption scheme, earlier versions of Excel cannot validate the password, and you are alerted that the password is not correct. When the workbook is shared, you cannot change the encryption type or the password. The corresponding elements in the Security and Save Options dialog boxes are disabled when the workbook is shared.

You cannot share a workbook that has a Weak Encryption (XOR) setting on a system that has regional settings other than French (Standard). When you try to share the workbook with XOR encryption, you receive a message that the workbook has been password-protected with an XOR encryption scheme and cannot be shared in this region.

Using Information Rights Management (IRM, Excel 2003 only)

IRM functionality in Microsoft Office Professional Edition 2003 can help protect sensitive internal business information, such as confidential planning documents or financial reports. You can set policies that wield more control over who can open, copy, print, or forward information created in Microsoft Office Word 2003, Excel 2003, Microsoft Office PowerPoint 2003, and Microsoft Office Outlook 2003.

With IRM, you can set different levels of file protection.

• Set file permissions at different levels and change the level for specific users and groups of users.
• Assign permissions according to roles and responsibilities. For example, set different permissions for a viewer, a reviewer, or a file editor.
• Restrict file printing to reduce the number of hard copies that are being produced.
• Set expiration dates to provide a time limit. After the time limit, a file can no longer be opened.
• Help to prevent forwarded files from being opened by an unauthorized recipient. Recipients cannot open files that are protected with IRM; instead, a message informs them that they do not have access rights.

For more information about IRM, visit the following Microsoft Web site:

For more information about removing passwords and protection from your documents, click Microsoft Excel Help on the Help menu, type remove protection and passwords in the Search for box in the Assistance pane, and then click Start searching to view the topic.

For more information about how to protect cell ranges, click the following article number to view the article in the Microsoft Knowledge Base: