Change or remove worksheet or workbook element protection

After you protect worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) or workbook (workbook: A spreadsheet program file that you create in Excel. A workbook contains worksheets of rows and columns in which you can enter and calculate data.) elements (with or without a password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.)), 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.

 Important   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

  1. Click the protected worksheet.
  2. On the Review tab, in the Changes group, click Unprotect Sheet.

Excel Ribbon image

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

  1. If you are prompted, type the worksheet element protection password, and then click OK.
  2. On the Review tab, in the Changes group, click Protect Sheet.
  3. 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.
  4. 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.

 Important   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 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.

Excel Ribbon image

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

  1. If you are prompted, type the worksheet element protection password, and then click OK.
  2. On the Review tab, in the Changes group, click Allow Users to Edit Ranges.
  3. In the Ranges unlocked by a password when sheet is protected box, click the range for which you want to change the password.
  4. Click Modify, and then click Password.
  5. In the New password box, type the new password for the range.
  6. In the Confirm new password box, type the same password, and then click OK twice.
  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.

 Important   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 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.

Excel Ribbon image

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

  1. If you are prompted, type the worksheet element protection password, and then click OK.
  2. On the Review tab, in the Changes group, click Allow Users to Edit Ranges.
  3. 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.
  1. When you are finished making changes in the Allow Users to Edit Ranges dialog box, click OK.
  2. On the Review tab, in the Changes group, click Protect Sheet.
  3. 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.

     Important   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 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.

Excel Ribbon image

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

  1. If you are prompted, type the workbook element protection password, and then click OK.
  2. On the Review tab, in the Changes group, click Protect Workbook.
  3. Under Restrict Editing, click Protect Structure and Windows.
  4. Under Protect workbook for, select or clear the Structure or Windows check boxes.
  5. 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.

 Important   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 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.

Excel Ribbon image

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

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

Top of Page 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.

Excel Ribbon image

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

  1. If you are prompted, type the worksheet element protection password, and then click OK.
  2. On the Review tab, in the Changes group, click Allow Users to Edit Ranges.
  3. In the Ranges unlocked by a password when cell is protected list, click the range for which you want to remove permissions.
  4. Click Delete.

Top of Page 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.

Excel Ribbon image

  1. If you are prompted, type the workbook element protection password, and then click OK.
  2. Under Restrict Editing, click Protect Structure and Windows to remove the check mark.

Top of Page Top of Page

Remove the protection from a shared workbook

 Important   After you remove protection from a shared workbook (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users.) 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 (change history: In a shared workbook, information that is maintained about changes made in past editing sessions. The information includes the name of the person who made each change, when the change was made, and what data was changed.) 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.

  1. If you are prompted, enter the password for the shared workbook.
  2. If you are prompted about the effects of removing protection on other users, click Yes.

Top of Page Top of Page

 
 
Applies to:
Excel 2007