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

This article describes how to apply different passwords to protect specific ranges of a Microsoft Office Excel 2007 worksheet. If you use any of the following versions of Microsoft Windows, you can assign permissions instead of passwords to separate ranges in a worksheet:

• Windows Vista
• Microsoft Windows Server 2003
• Microsoft Windows XP
• Microsoft Windows 2000

How to apply different passwords

To apply different passwords to separate ranges in an Excel 2007 worksheet, follow these steps:

1. Open a blank worksheet in Excel 2007.
2. On the Review tab, click Allow Users to Edit Ranges.
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 and Confirm Password boxes, type rangeone, and then click OK.
6. Repeat steps 3 through 5. However, select the range D2:D6, and then type rangetwo as the password for that range.
7. In the Allow Users to Edit Ranges dialog box, click Protect sheet.
8. In the Password to unprotect sheet box, type ranger, and then click OK two times.
   
   Note Retype the password when you are prompted.
9. Select cell B3, and then start to type Dataone.
   
   Note When you start to type, the Unlock Range dialog box appears.
10. Type rangeone in the Enter the password to change this cell box, and then press ENTER.
    
    Notes
    • You can now enter data in cell B3 or in any cell in the range B2:B6. However, you must type the correct password to enter data in any of the cells in the range D2:D6.
    • The range that you protect with a password does not have to contain adjacent cells. If you want the ranges B2:B6 and D2:D6 to share a password, select B2:B6 as described in step 4, type a comma in the New Range dialog box, and then select the range D2:D6 before you assign the password.

When you use this method to apply different passwords to separate ranges, a range that has been unlocked remains unlocked until the workbook is closed. When you unlock another range, the first range is not automatically relocked. Additionally, when you save the worksheet, ranges that you unlocked are not automatically relocked.

You can use the name of an existing range to identify cells that you want to protect with a password. However, Excel converts any relative references in the existing name definitions to absolute references. Because this behavior may produce unintended results, it is best to use the Collapse Dialog option to select the cells as described in step 4.

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

If you use any of the following versions of Windows, you can assign different permissions to individual users or to groups of users:

• Windows Vista
• Windows Server 2003
• Windows XP
• Windows 2000

When you apply group-level passwords or user-level passwords, permitted users can edit the protected ranges without having to type passwords. Other users must type the correct password to edit the protected ranges.

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

1. Open a blank worksheet in Excel 2007.
2. On the Review tab, click Allow Users to Edit Ranges.
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 two times.
   
   Note Retype the password when you are prompted.
6. Repeat steps 3 through 5. However, select the range D2:D6, and then type 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, and then click OK two times.
9. 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 two times.
   
   Note Retype the password when you are prompted.
10. Select cell B3, and then start to type Dataone.
    
    Note A password is required to edit the cell.
11. In the Unlock Range dialog box, click Cancel.
12. Select cell D3, and then type Datatwo.
    
    Notes
    • No password is required for the cell.
    • When you apply group-level permissions or user-level permissions and then open the workbook on a computer that is running an earlier version of Windows, the group-level permissions or the user-level permissions are ignored. However, passwords for different ranges are recognized.

Important Consider the following aspects when you apply passwords to specific ranges:

• Passwords have no effect unless the worksheet itself is protected.
• Passwords do not affect cells that have a Locked property of False.
• If you use the same password for more than one range, when you unlock one range, you unlock the other ranges that use the same password.
• If you try to paste data from a range of cells to a part of a worksheet that includes ranges that are protected with different passwords, you receive a message that states that more than one password is necessary to complete the operation.
• You can assign individual permissions that conflict with group permissions. When this occurs, the Deny permission takes priority over the Allow permission. Additionally, the user must to type a password to edit cells in the protected range.