27.1 How Excel Protection Works — The Two-Step System

Excel protection is a two-step system that most users misunderstand. Both steps must be completed for protection to work correctly.

The Key Insight

27.2 Locking and Unlocking Cells (Step 1)

The Correct Workflow

1. Select ALL cells (Ctrl+A) → Format Cells → Protection tab → untick Locked → OK. This unlocks every cell on the sheet.
2. Select only the cells that should be protected (formula cells, header cells, cells users must not touch)
3. Format Cells → Protection tab → tick Locked → OK. Now only those cells are locked.
4. Review → Protect Sheet (Step 2) to activate

Three Ways to Access the Locked Setting

The Hidden Property

The Protection tab in Format Cells has a second tick box: Hidden.

• When a cell is set to Hidden AND the sheet is protected: the formula in that cell does not appear in the Formula Bar when the cell is selected — users can see the result but not the formula
• Use this to protect proprietary formulas (commission calculations, pricing models) from being seen or copied
• Hidden only hides the formula display — the calculated result remains visible in the cell

27.3 Protecting a Sheet (Step 2)

Enabling Sheet Protection

1. Review tab → Protect group → Protect Sheet
2. — or — right-click the sheet tab → Protect Sheet…
3. The Protect Sheet dialog opens

Protect Sheet Dialog Options

Recommended Settings for Common Scenarios

Unprotecting a Sheet

• Review → Unprotect Sheet (if no password was set, this unprotects immediately)
• If a password was set, a dialog prompts for the password → enter it → OK
• — or — right-click the sheet tab → Unprotect Sheet

27.4 Allow Users to Edit Ranges

Allow Users to Edit Ranges lets you define specific cell ranges that different users (or anyone) can edit while the sheet is protected — optionally with a range-specific password different from the sheet protection password. This is useful in shared workbooks where different departments should edit their own sections.

Setting Up Editable Ranges

1. Before protecting the sheet: Review tab → Protect group → Allow Edit Ranges
2. Click New… in the Allow Users to Edit Ranges dialog
3. Set:
   • Title: a name for the range (e.g., "Finance Team Input")
   • Refers to cells: the cell range users can edit (e.g., C5:C20)
   • Range password: optional — a password specific to this range. Users who know this password can edit the range even when the sheet is protected. Leave blank to allow anyone to edit.
4. Click OK → repeat for additional ranges → click Protect Sheet to apply

Use Cases for Editable Ranges

27.5 Protecting the Workbook Structure

Workbook protection prevents changes to the workbook's structure: adding, deleting, renaming, moving, hiding, or unhiding sheets. It does not lock cell content — that is handled by sheet protection.

Enabling Workbook Protection

1. Review tab → Protect group → Protect Workbook
2. The Protect Structure and Windows dialog opens:
   • Structure: prevents adding, deleting, renaming, moving, or unhiding sheets. This is the important one — tick this.
   • Windows: prevents resizing or moving the workbook window (rarely needed; not available in Excel 365)
3. Enter an optional password → OK

What Workbook Protection Prevents

• Inserting new sheets
• Deleting sheets
• Renaming sheets
• Moving or copying sheets (within or between workbooks)
• Hiding or unhiding sheets — critical for dashboards where Data and Calculations sheets are hidden
• Changing the tab colour of sheets

What Workbook Protection Does NOT Prevent

• Editing cell content on any sheet (sheet protection handles this)
• Formatting changes
• Adding or editing charts, shapes, or images

Combined Protection: Sheet + Workbook

27.6 File-Level Password Encryption

File encryption requires a password to open the workbook at all. This is the strongest protection Excel offers — without the password, the file cannot be opened or read.

Setting a File Open Password

1. File → Info → Protect Workbook → Encrypt with Password
2. Type a strong password → OK → confirm the password → OK
3. Save the file. The password protection takes effect when the file is next opened.
4. When opened, Excel prompts for the password before the file contents are visible

Save As Password (Alternative Method)

1. File → Save As → Browse → click Tools (bottom of Save dialog) → General Options…
2. Two fields:
   • Password to open: required to open the file
   • Password to modify: required to save changes (file opens read-only without this password)
3. Enter passwords → OK → Save

Removing File Encryption

1. Open the file (enter password)
2. File → Info → Protect Workbook → Encrypt with Password
3. Clear the password field (leave it blank) → OK → Save
4. The file is now unencrypted

27.7 Mark as Final & Other Workbook Protection Options

Mark as Final

File → Info → Protect Workbook → Mark as Final sets the file to read-only and displays a yellow banner at the top indicating the document is finalised. This is a soft protection:

• Clicking "Edit Anyway" in the banner immediately removes Mark as Final
• Useful as a signal that the file is a finished version, not for security
• Prevents accidental edits when distributing a final report

Restrict Access (Information Rights Management)

• File → Info → Protect Workbook → Restrict Access
• Requires Microsoft 365 with Azure Rights Management or an on-premises IRM server
• Allows permissions to be tied to specific user accounts (Microsoft accounts or Active Directory accounts)
• Can prevent printing, copying, forwarding, or setting an expiry date on access
• The most robust enterprise-level document protection for sensitive payroll or financial data

Digital Signature

• File → Info → Protect Workbook → Add a Digital Signature
• Attaches a digital certificate to the workbook proving it has not been altered since signing
• If the file is changed after signing, the signature becomes invalid
• Used for compliance, audit trails, and regulatory submissions

27.8 Practical SA Protection Scenarios

Scenario 1: Payroll Template (HR sends to managers to fill in hours)

Scenario 2: Dashboard (Slicers work, nothing else editable)

Scenario 3: Shared Invoice Template (Formula cells hidden, input cells open)

27.9 Quick Self-Check

Q1: You protect a sheet but then discover that every single cell — including the cells users should be able to type into — is locked and cannot be edited. What went wrong and how do you fix it?

✓ The mistake is protecting the sheet without first unlocking the input cells. Every Excel cell is Locked by default. When the sheet is protected, all cells become un-editable because they are all locked. Fix: unprotect the sheet (Review → Unprotect Sheet) → select ALL cells (Ctrl+A) → Format Cells (Ctrl+1) → Protection tab → untick Locked → OK. This unlocks everything. Then select only the cells that should be protected (formula cells, headers) → Format Cells → Protection → tick Locked. Finally, protect the sheet again (Review → Protect Sheet). Now only formula cells are locked; input cells are freely editable.

Q2: You have a protected dashboard with Slicers, but after protecting the sheet the Slicers no longer respond when clicked. What permission did you forget to enable?

✓ The "Use PivotTable & PivotChart" permission was not ticked in the Protect Sheet dialog. Slicer interaction is classified as a PivotTable operation in Excel, so it requires this permission to be enabled. Fix: Review → Unprotect Sheet → Review → Protect Sheet again → in the dialog, tick "Use PivotTable & PivotChart" (and "Use AutoFilter" if a Timeline is present) → OK. The Slicers now respond to clicks while all other cells remain protected.

Q3: What is the difference between protecting a sheet and protecting a workbook? Can you do both at the same time?

✓ Sheet protection locks individual cell content and controls what users can do on a specific sheet (edit cells, sort, filter, use Slicers). It is applied per-sheet and each sheet can have different protection settings and passwords. Workbook protection locks the structure: it prevents adding, deleting, renaming, moving, hiding, or unhiding sheets. It does not affect cell content. Yes, both can be active simultaneously — and for a complete dashboard this is the recommended setup: protect each sheet to lock formulas, AND protect the workbook structure to prevent users from unhiding the Data or Calculations sheets that are hidden behind the Dashboard. They are completely independent layers.

Q4: A colleague needs to be able to edit cells B5:B20 on a protected sheet but no other cells. The rest of the sheet should be fully locked. How do you set this up?

✓ Two approaches: Approach 1 (simple unlock): Ctrl+A → Format Cells → Protection → untick Locked (unlock all) → OK. Then select all cells EXCEPT B5:B20 → Format Cells → tick Locked. Then Review → Protect Sheet. Now only B5:B20 are unlocked and editable. Approach 2 (Allow Edit Ranges for more control): Review → Allow Edit Ranges → New → set Refers to cells = $B$5:$B$20, optionally set a range password specific to this range → OK. Then protect the sheet. This approach allows you to set a different password for just that range and create multiple named editable ranges for different users or departments.

Q5: You want to prevent users from seeing the VLOOKUP formula in cell D5 that contains your confidential pricing logic. How do you hide the formula while keeping the result visible?

✓ Select cell D5 → Format Cells (Ctrl+1) → Protection tab → tick BOTH Locked AND Hidden → OK. Then protect the sheet (Review → Protect Sheet). After protection, clicking cell D5 shows the calculated result in the cell as normal, but the Formula Bar is completely blank — the VLOOKUP formula is invisible. Without the Hidden setting, clicking the cell would still show the formula in the Formula Bar even though the cell cannot be edited. The Hidden setting specifically suppresses the Formula Bar display. Note: this does not encrypt the formula — a user who unprotects the sheet (if they know the password) can see it. For genuinely confidential calculations, consider using a VBA function or keeping the logic in a database.

Q6: What is the difference between file encryption (Encrypt with Password) and sheet/workbook protection? Which should you use for a payroll file containing salaries?

✓ Sheet and workbook protection prevent editing and structural changes but do not prevent the file from being opened — anyone can open the file and see all data. File encryption (File → Info → Protect Workbook → Encrypt with Password) requires a password to open the file at all; without it, the file's contents are completely inaccessible. For a payroll file containing employee salaries, use BOTH: encrypt the file with a password (so only authorised staff can open it) AND protect the sheets (so that once opened, formula cells cannot be accidentally overwritten). Sheet protection alone is insufficient for sensitive payroll data because the file can be freely opened and all salary data read. File encryption is the appropriate security layer for confidential data; sheet protection is for preventing accidental edits.

✓ Module 27 Complete — You Have Learned:

• The two-step protection system — Step 1: set Locked/Unlocked on cells (no effect alone); Step 2: Protect Sheet (activates Step 1); all cells are Locked by default; correct workflow: unlock all → re-lock formula cells → protect sheet
• Locking and unlocking cells — 3 access methods (Ctrl+1 Protection tab, Home → Format → Lock Cell, right-click); the Hidden property (hides formula from Formula Bar while keeping the result visible; used for proprietary pricing or commission formulas)
• Protect Sheet dialog — all 12 permission checkboxes; recommended settings for data entry forms, filtered reports, interactive dashboards (must tick Use PivotTable & PivotChart for Slicers), and read-only views; unprotecting (with or without password); password security warning (Excel passwords are deterrents not guarantees)
• Allow Users to Edit Ranges — Review → Allow Edit Ranges → New; Title, Refers to cells, Range password; multiple named ranges for different departments; range password different from sheet password
• Workbook protection — Review → Protect Workbook → Structure; prevents adding/deleting/renaming/moving/hiding/unhiding sheets; what it does NOT prevent (cell content editing); combined sheet + workbook + hide sheets = fully secured dashboard
• File encryption — File → Info → Protect Workbook → Encrypt with Password (strongest protection; file cannot be opened without password); Save As → Tools → General Options (Password to open + Password to modify); critical: forgotten password = permanently inaccessible; removing encryption (clear the password field)
• Mark as Final — soft read-only with yellow banner; "Edit Anyway" removes it instantly; signal not security; Restrict Access (IRM/Azure RMS for enterprise permissions per user); Digital Signature (tamper detection for compliance and audit)
• SA scenarios — payroll template (managers fill hours, salary formulas locked and hidden); dashboard protection (Slicers active, no cell edits, workbook structure locked); shared invoice template (Hidden property conceals pricing formula from client)