Topic 11

Protection in Excel

Excel has three distinct layers of protection — each guarding something different. Understanding which layer does what is essential before applying any of them, because they are commonly confused with each other.

3 protection levels No practice file needed

Three Layers — Three Different Jobs

Each layer protects something different. They can be used independently or combined.

🔐
Password to Open
Encrypts the entire file. No one can even open it without the password.
File access
🛡️
Protect Sheet
Controls what users can do inside a sheet — which cells they can edit, format, or delete.
Content
📋
Protect Workbook
Locks the workbook structure — prevents adding, deleting, renaming, or moving sheets.
Structure

🔐

Level 1

Password to Open

Encrypts the file — no access without the password

When you set a password to open, Excel encrypts the entire file using AES-256. The file cannot be opened — or even previewed — without entering the correct password first.

How to set it

Alternative path through Save As:

How to remove it

Same path — open the dialog, clear the password field, and save.

⚠️

If you forget the password — the file is gone

AES-256 encryption means there is no recovery option. If the password is lost, the file cannot be opened by anyone — including Microsoft support. Always store the password securely before saving.

🛡️

Level 2

Protect Sheet

Controls which cells users can edit on a specific sheet

Protect Sheet prevents changes to cell content and formatting. But there is a critical concept to understand first: all cells are "locked" by default — but this locked flag only takes effect when you turn on sheet protection. To allow editing in specific cells, you must unlock them first.

The counterintuitive part — unlock what you want editable

Default — all cells locked

A1 B2 C3 D4 E5

Before protection is on, "locked" has no effect — everyone can edit everything.

→

After unlocking input cells

A1 B2 C3 D4 E5

B2 and D4 are unlocked — when sheet is protected, only these can be edited.

Workflow

Select the cells you want users to be able to edit.

Right-click → Format Cells → Protection tab → uncheck "Locked" → OK. These cells are now unlocked.

Go to Review tab → Protect Sheet. Set an optional password. Choose what actions users are allowed (default is sufficient for most cases).

Click OK. Now users can only edit the cells you unlocked in step 1 — everything else is blocked.

What the dialog controls

The checkboxes in the Protect Sheet dialog define what users are allowed to do even while protection is on.

Action	Default	Notes
Select locked cells	✓ Allowed	Users can click on them but not edit
Select unlocked cells	✓ Allowed	Users can click and edit these
Format cells	✗ Blocked	Can be enabled if needed
Insert/Delete rows	✗ Blocked	Enable only if required
Sort / Filter	✗ Blocked	Enable for data-entry sheets

💡

Unprotect sheet

Review → Unprotect Sheet. If a password was set, you'll be prompted for it. After unprotecting, all cells are editable again.

📋

Level 3

Protect Workbook

Locks the workbook structure — sheets cannot be added, deleted, or renamed

Protect Workbook prevents changes to the structure of the workbook — not the content of cells. Users can still read and edit cell values normally; they just cannot reorganise the sheets.

Action blocked when Protect Workbook is on
✗ Insert or delete sheets
✗ Rename sheets
✗ Move or copy sheets
✗ Hide or unhide sheets
✓ Edit cell contents — still allowed (Protect Sheet handles this)

📌

The key distinction

Protect Sheet = controls what happens inside a sheet (cells).
Protect Workbook = controls what happens to the sheets themselves (tabs).
They are independent — you can have one without the other, or both at the same time.

Side-by-Side Comparison

Question	Password to Open	Protect Sheet	Protect Workbook
What does it protect?	File access	Cell contents	Sheet structure
Where to find it?	File → Info → Protect	Review tab	Review tab
Password required?	Yes	Optional	Optional
Prevents editing cells?	N/A (can't open)	Yes (locked cells)	No
Prevents adding sheets?	N/A (can't open)	No	Yes
Encryption?	Yes (AES-256)	No	No

Typical Combined Workflow

When sharing a workbook with input cells, this is the standard approach:

Unlock the input cells

Select the cells users need to fill in → Format Cells → Protection → uncheck Locked.

Protect the sheet

Review → Protect Sheet. Users can now only edit the unlocked cells from step 1.

Protect the workbook structure

Review → Protect Workbook. Users cannot accidentally delete or rename the sheet.

Add a password to open (if sensitive)

File → Info → Encrypt with Password. Only needed when the data is confidential. Store the password somewhere safe before saving.

🔑

Important — Know the limits

Sheet and Workbook protection are not high security

Protect Sheet and Protect Workbook are designed to prevent accidental changes — not malicious ones. A determined user with the right tool can bypass them. They are organisational guardrails, not encryption.

Password to Open is the exception — it uses real AES-256 encryption and cannot be bypassed. Use it when the data itself must remain confidential.

Topic 10 — Logical Functions

Topic 12 — Excel Errors