Excel 2010: Password-Protect a Spreadsheet or Require a Password to Open It

Excel 2010 offers three different controls: Protect Sheet limits cell edits; Protect Workbook locks worksheet tabs; Encrypt with Password requires a password to open the file. Only the last option encrypts the workbook for access control—sheet and structure protection mainly prevent changes.

These steps use Excel 2010’s desktop interface.

Choose the right kind of protection

Microsoft notes that worksheet protection is not intended to be a security feature. A protected sheet remains viewable, and workbook-structure protection does not stop someone from opening the file. If you need an opening password, use file encryption.

Prepare cells that users should be able to edit

Excel marks cells as locked by default, but that setting does not take effect until you protect the worksheet. If the sheet is a form or template, unlock the entry cells before turning on protection:

1. Select the cells people should be able to change.
2. Press Ctrl+1 to open Format Cells, then select the Protection tab.
3. Clear Locked and click OK.

Leave formula and heading cells locked. If users should not edit any cell, skip this preparation and keep the default cell settings.

Password-protect a worksheet in Excel 2010

1. Select the worksheet you want to protect. On the Ribbon, open Review and click Protect Sheet in the Changes group.
2. In the dialog, leave Protect worksheet and contents of locked cells selected. Under Allow all users of this worksheet to, check only the actions users need, such as selecting unlocked cells, sorting, or using an existing AutoFilter. The checked options are permissions for users of the protected sheet.
3. Enter a password in Password to unprotect sheet if you want to require one to remove protection. Click OK, re-enter the password when prompted, and click OK again.

Try editing a locked cell to confirm the restriction, then check that the unlocked input cells still accept entries. To remove the protection later, open Review, click Unprotect Sheet, and enter the password if Excel requests it. Excel changes the command back to Protect Sheet after the sheet is unprotected.

Protect the workbook’s sheet structure

To prevent changes to sheet tabs, choose Review > Protect Workbook. In Excel 2010, the Protect Structure and Windows dialog appears. Select Structure to prevent users from adding, deleting, moving, copying, renaming, hiding, or unhiding worksheets. Enter a password, click OK, and confirm it in the next dialog. A password is optional, but without one another user can remove this protection.

The Windows checkbox is a separate, older option for workbook windows. Microsoft documents that Excel 2013 introduced a different window interface and no longer enforces workbook-window protection; an older workbook’s saved window settings do not prevent people from arranging or closing windows in those versions. For workbooks shared with newer Excel, rely on Structure only for sheet-tab restrictions. To remove workbook protection in Excel 2010, click Review > Protect Workbook again and supply the password.

Require a password to open the spreadsheet

1. Click the File tab, then select Info.
2. Choose Protect Workbook and select Encrypt with Password.
3. Enter a password in the prompt, click OK, and type it again to confirm.
4. Save the workbook. Close it and reopen it to confirm Excel asks for the password before showing the contents.

This file-level setting is the right choice when the goal is to keep a workbook from opening without authorization. It can be combined with sheet or structure protection, but those extra controls serve different purposes.

Keep the password safe and check restrictions

• If users can still edit a cell, check whether that cell was unlocked before sheet protection was enabled. Also review the allowed actions in the Protect Sheet dialog.
• If sheet-tab commands such as Rename or Delete are unavailable, workbook structure protection may be active.
• Store passwords in a password manager or another secure place. Microsoft says it cannot retrieve a forgotten Excel password; losing an opening password can leave the workbook inaccessible.
• For sensitive information, use the file-opening encryption option rather than treating worksheet or workbook protection as a security boundary.

For details, see Microsoft’s Protect a worksheet, Protect a workbook, Protect an Excel file, and Protection and security in Excel guidance. Microsoft’s Excel interface documentation explains why the Windows option was deprecated after Excel 2010.