How to apply different passwords or permissions to separate ranges in workbooks in Excel 2003 and in Excel 2002 (826924)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002

IN THIS TASK

Note The features in Microsoft Excel that are related to hiding data and protecting worksheets and workbooks with passwords are not intended to be mechanisms for securing data or protecting confidential information in Excel. You can use these features to present information more clearly by hiding data or formulas that might confuse some users. These features also help prevent other users from making accidental changes to data. Excel does not encrypt data that is hidden or locked in a workbook. With enough time, users can obtain and modify all the data in a workbook, as long as they have access to it. To help prevent modification of data and to help protect confidential information, limit access to any Excel files that contain such information by storing them in locations available only to authorized users.

SUMMARY

This article discusses how to apply different passwords to protect specific ranges of a worksheet in Microsoft Office Excel 2003 and in Microsoft Excel 2002.

In Microsoft Excel 2002 and later versions, you can use passwords to protect specific ranges in your worksheet. This is a change from earlier versions of Microsoft Excel, where one password applies to the whole worksheet, and the worksheet may have several protected ranges.

back to the top

How to apply different passwords

To apply different passwords to two ranges in a worksheet, follow these steps:
  1. Start Excel, and then open a blank workbook.
  2. On the Tools menu, point to Protection, and then 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 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, click OK, 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. When prompted, retype the password, and then click OK.
  8. Select cell B3, and then start to type Dataone.

    When you type D, the Unlock Range dialog box appears.
  9. Type rangeone in the Enter the password to change this cell box, and then click OK.

    You can now enter data in cell B3 and in 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 be made 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 workbook, 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 better to use the Collapse Dialog button to select the cells, as described earlier in this article.

back to the top

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

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 workbook.
  2. On the Tools menu, point to Protection, and then 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. When prompted, retype the password, 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 Permissions, and then click Add in the Permissions for Range2 dialog box.
  8. In the Select Users or Groups dialog box, type or select Everyone, and then click OK.
  9. 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. When prompted, retype the password, and then click OK.
  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.


back to the top

How to change passwords

To change the password for a range, follow these steps:
  1. Start Excel, and then open the workbook.
  2. On the Tools menu, point to Protection, and then click Unprotect Sheet.
  3. If prompted type the worksheet password, and then click OK.
  4. On the Tools menu, point to Protection, and then click Allow Users to Edit Ranges.
  5. Click a range in the list, and then click Modify.
  6. Click Password.
  7. Type the new password in the New password box, and then retype the new password in the Confirm new password box.
  8. Click OK, and then click OK.
  9. To change the password for another range, repeat steps 3 through 6. Otherwise, click Protect Sheet.
  10. Type the worksheet password in the Password to unprotect sheet box.
  11. Click OK, retype the worksheet password to confirm it, and then click OK.

back to the top

How to unprotect ranges


For more information about how to remove 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.

back to the top

Important

Note these aspects of applying passwords and group-level permissions to specific ranges:
  • Excel 2003 runs only on Windows XP and Windows 2000.
  • When a workbook with protected ranges is opened in Excel 2002 on a Microsoft Windows XP-based computer, on a Microsoft Windows 2000-based computer, or on a Microsoft Windows NT-based computer, the worksheet range and group protection are the same as they are in Excel 2003.
  • When a workbook with protected ranges is opened in Excel 2002 on a Microsoft Windows Millennium Edition-based computer or on a Microsoft Windows 98-based computer, ranges with user-level and group-level permissions require the range password.

back to the top

REFERENCES

For more information about permissions and password protection, click Microsoft Excel Help on the Help menu, type passwords in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about overlapping protection, click the following article number to view the article in the Microsoft Knowledge Base:

277615 Error message when you attempt to edit multiple cells in a protected worksheet with a single password


back to the top
Modification Type:MinorLast Reviewed:2/8/2006
Keywords:KB826924
©2004 Microsoft Corporation. All rights reserved.