Change or remove worksheet or workbook element protection

After you protect worksheet or workbook elements (with or without a password), you may want to make changes to those elements or passwords. Although setting a password is optional when you protect an element, it is a valuable part of element protection. You may also want to remove the element protection so that you can make unrestricted changes to all of the data and elements.

  Worksheet and workbook element protection should not be confused with workbook-level password security. Element protection cannot protect a workbook from users who have malicious intent. For increased security, you should help protect your whole workbook file by using a password. This helps prevent unauthorized users from viewing or modifying workbook data in Excel.

For information about workbook-level password protection, see Set a password to open or modify a document, workbook, or presentation.

What do you want to do?

Change the worksheet elements that you want to protect

Change the password for unlocking a range of cells

Change user permissions for a specific range

Change the workbook elements that you want to protect

Remove the worksheet element protection

Remove user permissions for a specific range

Remove the workbook element protection

Remove the protection from a shared workbook

Change the worksheet elements that you want to protect

  1. Click the protected worksheet.

  2. On the Review tab, in the Changes group, click Unprotect Sheet.

    The Changes group on the Review tab

    Note   Unprotect Sheet is available only when a worksheet is protected.

  3. If you are prompted, type the worksheet element protection password, and then click OK.

  4. On the Review tab, in the Changes group, click Protect Sheet.

  5. In the Allow all users of this worksheet to list, do the following:

    • Select the check boxes of any actions that you want users to be able to perform on worksheet elements.

    • Clear the check boxes of any actions that you do not want users to be able to perform on worksheet elements.

  6. In the Password to unprotect sheet box, type the worksheet element protection password that you want to use, and then click OK.

    If you are prompted, retype the worksheet element protection password to confirm it.

      Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Use a strong password that you can remember so that you don't have to write it down.

Top of Page

Change the password for unlocking a range of cells

  1. Click the protected worksheet.

  2. On the Review tab, in the Changes group, click Unprotect Sheet.

    The Changes group on the Review tab

    Note   Unprotect Sheet is available only when a worksheet is protected.

  3. If you are prompted, type the worksheet element protection password, and then click OK.

  4. On the Review tab, in the Changes group, click Allow Users to Edit Ranges.

  5. In the Ranges unlocked by a password when sheet is protected box, click the range for which you want to change the password.

  6. Click Modify, and then click Password.

  7. In the New password box, type the new password for the range.

  8. In the Confirm new password box, type the same password, and then click OK twice.

  9. On the Review tab, in the Changes group, click Protect Sheet.

  10. In the Password to unprotect sheet box, type the worksheet element protection password that you want to use, and then click OK.

    If you are prompted, retype the worksheet element protection password to confirm it.

      Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Use a strong password that you can remember so that you don't have to write it down.

Top of Page

Change user permissions for a specific range

  1. Click the protected worksheet.

  2. On the Review tab, in the Changes group, click Unprotect Sheet.

    The Changes group on the Review tab

    Note   Unprotect Sheet is available only when a worksheet is protected.

  3. If you are prompted, type the worksheet element protection password, and then click OK.

  4. On the Review tab, in the Changes group, click Allow Users to Edit Ranges.

  5. Do one of the following:

    • To change which users are allowed to edit a specific range, do the following:

      1. In the Ranges unlocked by a password when sheet is protected box, click the range for which you want to change the users.

      2. Click Modify, and then click Permissions.

      3. Do one or more of the following:

        • To temporarily block a user from editing the range, click the user in the Name list, and then select the Deny check box for the user.

        • To unblock a user, click the user in the Name list, and then select the Allow check box for the user.

        • To permanently remove a user, click the user in the Name list, and then click Remove.

        • To add a new user, click Add, locate and select the new user, click Add, and then click OK.

      4. Click OK, click Yes if you are prompted about denying users, and then click OK again.

    • To change the cells that are included in the range, do the following:

      1. In the Ranges unlocked by a password when sheet is protected box, click the range for which you want to change the included cells.

      2. Click Modify.

      3. In the Refers to cells box, type or select the new range, and then click OK.

  6. When you are finished making changes in the Allow Users to Edit Ranges dialog box, click OK.

  7. On the Review tab, in the Changes group, click Protect Sheet.

  8. In the Password to unprotect sheet box, type the worksheet element protection password that you want to use, and then click OK.
    If you are prompted, retype the worksheet element protection password to confirm it.

      Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Use a strong password that you can remember so that you don't have to write it down.

Top of Page

Change the workbook elements that you want to protect

You can protect the structure of a workbook so that its worksheets or other elements cannot be deleted, hidden, unhidden, moved, or renamed. You can also prevent the tab colors of worksheets from being changed, and prevent new worksheets, charts, and other elements from being inserted.

You can also protect windows in the workbook so that their size and position cannot be changed.

  1. Open the protected workbook.

  2. On the Review tab, in the Changes group, click Unprotect Workbook.

    The Changes group on the Review tab

    Note   Unprotect Workbook is available only when a workbook is protected.

  3. If you are prompted, type the workbook element protection password, and then click OK.

  4. On the Review tab, in the Changes group, click Protect Workbook.

  5. Under Restrict Editing, click Protect Structure and Windows.

  6. Under Protect workbook for, select or clear the Structure or Windows check boxes.

  7. In the Password (optional) box, type the password that you want to use, and then click OK.

    If you are prompted, click OK to confirm the password.

      Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Use a strong password that you can remember so that you don't have to write it down.

    Tip   You can leave the box blank if you do not want to use a password.

Top of Page

Remove the worksheet element protection

  1. Click the protected worksheet.

  2. On the Review tab, in the Changes group, click Unprotect Sheet.

    The Changes group on the Review tab

    Note   Unprotect Sheet is available only when a worksheet is protected.

  3. If you are prompted, type the worksheet element password, and then click OK.

Top of Page

Remove user permissions for a specific range

  1. Click the protected worksheet.

  2. On the Review tab, in the Changes group, click Unprotect Sheet.

    The Changes group on the Review tab

    Note   Unprotect Sheet is available only when a worksheet is protected.

  3. If you are prompted, type the worksheet element protection password, and then click OK.

  4. On the Review tab, in the Changes group, click Allow Users to Edit Ranges.

  5. In the Ranges unlocked by a password when cell is protected list, click the range for which you want to remove permissions.

  6. Click Delete.

Top of Page

Remove the workbook element protection

  1. Open the protected workbook.

  2. On the Review tab, in the Changes group, click Protect Workbook.

    The Changes group on the Review tab

  3. If you are prompted, type the workbook element protection password, and then click OK.

  4. Under Restrict Editing, click Protect Structure and Windows to remove the check mark.

Top of Page

Remove the protection from a shared workbook

  After you remove protection from a shared workbook when a password is in use, the shared workbook will no longer be shared. Other users will be disconnected from the workbook, and the change history will be erased. If no password is in effect, the workbook remains shared after you unprotect it.

  1. Open the protected shared workbook.

  2. On the Review tab, in the Changes group, click Unprotect Shared Workbook.

    Excel Ribbon Image

    Note   Unprotect Shared Workbook is available only when a shared workbook is protected.

  3. If you are prompted, enter the password for the shared workbook.

  4. If you are prompted about the effects of removing protection on other users, click Yes.

Top of Page

Applies To: Excel 2007



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language