How to apply different passwords or permissions to separate ranges in worksheets in Excel

This article shows you how to apply different passwords to protect specific ranges of a worksheet. If you use Microsoft Windows 2000, you can assign permissions rather than passwords to separate ranges of your worksheets.

In Microsoft Excel 2002 and in later versions of Excel, you can now use passwords to protect specific ranges in your worksheets. This is a change from earlier versions of Excel, in which one password applies to the entire worksheet, which might have several protected ranges. In addition, if you use Windows 2000, you can apply group-level passwords and user-level passwords to different ranges.

Note This article describes how to enable specific collaboration scenarios to function correctly in collaboration environments that do not include users who have malicious intent. You cannot enable strong encryption for a file by using password protection. To protect your document or file from a user who has malicious intent, you can restrict permission by using Information Rights Management (IRM).

How to apply separate passwords

To apply different passwords to two ranges in a worksheet, follow these steps:

1. Start Excel, and then open a blank worksheet.
2. On the Tools menu, point to Protection, and then click Allow Users to Edit Ranges.
   
   Note If you are running Microsoft Office Excel 2007, click Allow Users to Edit Ranges on the Review menu in the Changes group.
3. In the Allow Users to Edit Ranges dialog box, click New.
4. In the New Range dialog box, click the Collapse Dialog button. Select the range B2:B6, and then click the Collapse Dialog button again.
5. In the Range password box, type rangeone, then type it again in the Confirm Password dialog box, and then click OK.
6. Repeat steps 3 through 5, selecting the range D2:D6 and typing rangetwo as the password for that range.
7. In the Allow Users to Edit Ranges dialog box, click Protect sheet. In the Password to unprotect sheet box, type ranger, and then click OK two times. When prompted, retype the password.
8. Select cell B3, and then start to type Dataone.
   
   Notice that as soon as you type D, the Unlock Range dialog box appears.
9. Type rangeone in the Enter the password to change this cell box, and then press ENTER.
   
   You can now enter data in cell B3 as well as any other cell in the range B2:B6, but you cannot enter data in any of the cells D2:D6 without first providing the correct password for that range.

The range that you protect with a password does not have to consist of adjacent cells. If you want the ranges B2:B6 and D2:D6 to share a password, you can select B2:B6 as described in step 4 earlier in this article, type a comma in the New Range dialog box, and then select the range D2:D6 before you assign the password.

When you apply different passwords to separate ranges in this way, a range that has been unlocked remains unlocked until the workbook is closed. When you unlock another range, you do not relock the first range. Likewise, when you save a worksheet, you do not relock a range.

You can use existing range names to identify cells that are to be protected with passwords, but if you do, Excel converts any relative references in the existing name definitions to absolute references. Because this may not give you the results you intended, it is preferable to use the Collapse Dialog button to select the cells, as described earlier in this article.

How to apply group-level passwords and user-level passwords

If you use Windows 2000 (but not other versions of Windows), you can assign different permissions to various individual users or groups of users. When you do this, permitted users can edit the protected ranges without needing to type passwords, and other users can still edit the ranges as long as they can supply the correct password.

To apply group-level protection to a worksheet, follow these steps:

1. Start Excel, and then open a blank worksheet.
2. On the Tools menu, point to Protection, and then click Allow Users to Edit Ranges.
   
   Note If you are running Excel 2007, click Allow Users to Edit Ranges in the Changes group on the Review menu.
3. In the Allow Users to Edit Ranges dialog box, click New.
4. In the New Range dialog box, click Collapse Dialog, select the range B2:B6, and then click Collapse Dialog again.
5. In the Range password box, type rangeone, and then click OK twice. When prompted, retype the password.
6. Repeat steps 3 through 5, selecting the range D2:D6 and typing rangetwo as the password for that range.
7. In the Allow Users to Edit Ranges dialog box, click Permissions, and then click Add in the Permissions for Range2 dialog box.
8. In the Select Users, Computers, or Groups dialog box, type Everyone.
9. Click OK in the Select Users, Computers, or Groups dialog box, and then click OK in the Permissions for Range2 dialog box.
10. In the Allow Users to Edit Ranges dialog box, click Protect sheet, type ranger in the Password to unprotect sheet box, and then click OK twice. When prompted, retype the password.
11. Select cell B3, and then start to type Dataone. A password is still required. Click Cancel in the Unlock Range dialog box.
12. Select cell D3, and then type Datatwo.
    
    No password is required.

Note You must use Windows 2000 in order to assign permissions to groups or individuals as described earlier in this article, but after you have done so, those permissions are recognized when the worksheets are edited on computers that use Microsoft Windows NT. Windows NT does not enable you to assign or modify the permissions, however.

If you apply group permissions or user permissions, and then open the workbook in Excel 2002 on a Microsoft Windows Millennium Edition-based computer or Microsoft Windows 98-based computer, the group permissions or user permissions are ignored, but different passwords for different ranges are recognized.

Important

Note these aspects of applying passwords to specific ranges:

• Passwords have no effect unless the worksheet itself is protected.
• Passwords have no effect on cells whose Locked property is False.
• If you use the same password for more than one range, when you unlock one range, you unlock the others as well.
• If you try to paste data from a range of cells into a part of your worksheet that includes ranges protected with different passwords, you receive a message that states that more than one password is necessary to complete the operation.
• You can assign an individual permissions that conflict with the permissions that he or she has as a member of a group. If you do so, the Deny permission takes priority over the Allow permission, and the user is required to type a password to edit cells in the protected range.

For more information about Office features that are intended to enable collaboration, click the following article number to view the article in the Microsoft Knowledge Base: For more information about encryption using Information Rights Management (IRM), visit the following Microsoft Web site: