Protect an Excel Workbook: Passwords, Sheets, Cells and Structure

by

Excel has three kinds of protection, and they do very different things. A file password encrypts the workbook so it can't be opened. Sheet protection and workbook structure protection only stop accidental changes; they aren't security.

Can't open without passwordFile → Info → Encrypt with Password
Lock cellsReview → Protect Sheet
Lock sheet tabsReview → Protect Workbook

Which protection do you need?

Type Stops Strength
Encrypt with Password Anyone opening the file Strong (encrypted). A lost password can't be recovered
Protect Sheet Editing locked cells, formatting, inserting rows Weak: prevents mistakes, not determined people
Protect Workbook (structure) Adding, deleting, renaming, moving or unhiding sheets Weak

1. Password to open the file

  1. File → Info → Protect Workbook → Encrypt with Password.
  2. Type the password, then type it again.
  3. Save the file. Protection only takes effect after saving.
Protect Workbook options in File, Info
Encrypt Document dialog
Password prompt when opening the file

To remove it, open the same option, delete the password, click OK and save. Keep the password somewhere safe: Microsoft can't recover it.

2. Protect a sheet

Review → Protect Sheet, tick what users may still do, set a password (optional) and click OK.

Protect Sheet on the Review tab
Protect Sheet options
Warning when editing a protected cell

Untick Select locked cells to stop people selecting (and copying) protected cells. To undo: Review → Unprotect Sheet.

3. Protect only some cells

Every cell is locked by default, but locking only matters once the sheet is protected. So unlock the cells people should edit, then protect the sheet:

  1. Select the input cells → Ctrl + 1 → Protection tab → untick Locked → OK.
  2. Review → Protect Sheet.
Locked checkbox on the Protection tab

To hide formulas from the formula bar too, tick Hidden on the same tab for the formula cells. To give different people different editable areas, use Review → Allow Edit Ranges.

4. Protect the workbook structure

Review → Protect Workbook, set a password and click OK. Sheets can no longer be inserted, deleted, renamed, moved, hidden or unhidden.

Protect Structure and Windows dialog
Sheet tab menu with options greyed out

Combine it with very hidden sheets to keep a lookup sheet out of sight.

Things to know

  • Sheet and structure passwords can be removed with freely available tools. Don't rely on them for confidential data; encrypt the file instead.
  • Protection settings carry over to Excel for the web, but you can't set a file password there.
  • To protect many sheets at once, use the protect worksheets macro.

Related