The first thing most people do when they need to lock an Excel document is double-click File > Info > Protect Workbook > Encrypt with Password. They type in 'Passw0rd123', hit OK, and call it done. Big mistake. That password only stops someone from opening the file—but if they get a copy, they can bypass it in under 60 seconds using free online tools. Worse? It breaks co-authoring, disables AutoSave in OneDrive/SharePoint, and makes recovery nearly impossible if the password is lost. I watched a finance analyst at Alibaba’s Shenzhen office lose three days of reconciliations because her encrypted file wouldn’t open after a Windows update. Don’t be that person.
Quick Answer
Locking an Excel document isn’t about passwords—it’s about controlling what people can change, not whether they can open it. Use worksheet protection (Alt + A + P) for cell-level control, workbook structure protection (Alt + R + S) to prevent sheet deletion, and SharePoint permissions for true access control. Combine them—and skip encryption entirely unless you’re archiving offline backups.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Worksheet Protection | Select cells → Right-click → Format Cells → Protection tab → Uncheck 'Locked' → Home tab → Protect Sheet (Alt + A + P) | Preventing edits to formulas, headers, or totals (e.g., B2:B10 in a payroll sheet) | No password = anyone can unprotect. With password = no audit trail if shared |
| Workbook Structure Protection | Review tab → Protect Workbook → Check 'Structure' → Enter password (Alt + R + S) | Stopping accidental sheet deletion or renaming (e.g., 'Q3 Budget' and 'Actuals' tabs) | Doesn’t protect cell content. Password still required—and easily cracked if reused elsewhere |
| SharePoint/OneDrive Permissions | Right-click file in SharePoint → Manage Access → Stop Inheriting → Assign 'View Only' or 'Can Edit' to specific people/groups | Teams sharing files across departments (e.g., HR sharing org charts with managers only) | Requires Microsoft 365 license. Won’t work on local network drives or USB sticks |
| Password Encryption (File-Level) | File → Info → Protect Workbook → Encrypt with Password → Type password → Save | Offline archival of sensitive files (e.g., merger due diligence docs on encrypted USB) | Breaks real-time co-authoring. No password recovery. Blocks AutoSave and version history |
| Read-Only Recommended (No Password) | File → Info → Protect Workbook → Mark as Final → Click 'OK' → Save | Discouraging edits in distribution copies (e.g., sending 'Final Q3 Report v2' to stakeholders) | Purely advisory—users can ignore it with one click. No technical enforcement |
Method 1 Deep Dive
Let’s walk through worksheet protection—the most practical way to lock *exactly what matters*. Open Payroll_Q3_2024.xlsx. You’ll see columns A–D: Employee (A2:A12), Role (B2:B12), Base Salary (C2:C12), and Bonus % (D2:D12). The formula in E2 is =C2*(1+D2)—and it’s copied down to E12. Your goal: let managers update Bonus %, but prevent changes to Base Salary or the formula.
First, unlock the editable cells. Select D2:D12 → Right-click → Format Cells → Protection tab → Uncheck Locked → OK. Then select C2:C12 and E2:E12 → same steps, but keep 'Locked' checked (it’s default). Now go to the Home tab → Protect Sheet (or press Alt + A + P). Leave the password field blank if this is internal-only, or enter 'HR2024!' if you need accountability. Check only these boxes: 'Select locked cells', 'Select unlocked cells', and 'Format cells'. Uncheck everything else—especially 'Edit objects' and 'Sort'. Click OK.
Try clicking C5 (Base Salary for Sarah Chen). You’ll get a warning: 'The cell is locked and the sheet is protected.' But click D7 (Bonus % for James Wong)—you can edit freely. That’s precise control. And here’s the counterintuitive tip: never protect the entire sheet before unlocking cells first. If you skip the unlock step, Excel locks *everything* by default—including cells you meant to leave editable. I wasted 45 minutes last week rebuilding a model because I forgot that step.
Sample data after protection:
| Employee | Role | Base Salary | Bonus % | Total Pay |
|---|---|---|---|---|
| Sarah Chen | Senior Analyst | $82,500 | 8.5% | $89,512.50 |
| James Wong | Team Lead | $114,200 | 12.0% | $127,904.00 |
| Priya Patel | Finance Manager | $142,000 | 15.5% | $164,010.00 |
| Diego Morales | Intern | $28,000 | 0.0% | $28,000.00 |
Method 2 Deep Dive
Now let’s protect the workbook *structure*. Open Acme_Corp_Budget_2024.xlsx. It has four sheets: 'Summary', 'Marketing', 'R&D', and 'Legal'. Finance wants to stop teams from deleting or renaming tabs—but still allow editing inside each sheet. Go to the Review tab → Protect Workbook (or press Alt + R + S). Check only Structure, not Windows. Enter 'FIN2024!' as the password. Click OK.
Try right-clicking the 'Marketing' tab → 'Delete'. You’ll see: 'The workbook is protected. To delete a sheet, first unprotect the workbook.' Same for dragging tabs or double-clicking names. But you can still edit cell A1 on the 'Legal' sheet freely. This method is lightweight, reversible, and doesn’t interfere with formulas or conditional formatting.
Crucially: this protection only applies to the current file instance. If someone saves a copy (Ctrl + S → 'Save As'), the copy has no protection—unless you reapply it. So always pair this with SharePoint permissions for external sharing. At Alibaba, we use this on master budget files stored in the 'Finance Shared' library—with 'View Only' set for non-finance staff and 'Can Edit' only for the FP&A team.
Cheat Sheet
| Action | Shortcut / Steps | When to Use It |
|---|---|---|
| Unlock specific cells | Select cells → Ctrl + 1 → Protection tab → Uncheck 'Locked' | Before protecting sheet—so users can edit those cells later |
| Protect current sheet | Alt + A + P → Enter password (optional) → Choose allowed actions | Locking formulas in B2:B10 while leaving D2:D10 editable |
| Protect workbook structure | Alt + R + S → Check 'Structure' → Enter password | Preventing sheet deletion in multi-tab reports like dashboards |
| Set SharePoint permissions | Right-click file in SharePoint → Manage Access → Stop Inheriting → Add person/group → Set 'View Only' | Controlling who sees or edits a live file used across teams |
| Remove all protection | Review tab → Unprotect Sheet / Unprotect Workbook → Enter password | When you need full access to adjust formulas or add new sheets |
