What You’ll Learn
- ✓ The three layers of Excel protection and when to use each one
- ✓ How to lock and unlock cells before protecting a sheet
- ✓ How to use Allow Edit Ranges for precise collaboration control
- ✓ The difference between sheet protection and real file encryption
- ✓ The security caveat most Excel tutorials never mention
Protect sheets and workbooks in Excel and you solve one of the most common, most avoidable frustrations in office life — the one where you send a carefully built spreadsheet to a colleague and get it back with three formulas deleted, two sheets renamed, and a pivot table that no longer works.
You’ve probably seen it happen. Maybe it happened to you this week. Someone didn’t mean to break anything. They just clicked around and things went wrong. The problem isn’t the colleague — it’s that the file had no protection on it at all.
Here’s the thing: there are three separate layers of protection in Excel, and each one does something different. Most users only discover one of them, use it halfway, and wonder why things still go wrong. This lesson covers all three — step by step — including a feature called Allow Edit Ranges that most intermediate users have never touched, even though it’s the most practical protection tool Excel has for real collaboration.
The Three Layers of Excel Protection — And Why They’re Not the Same
Before you touch any menus, take sixty seconds to understand the structure here. Most of the confusion around Excel protection comes from people not realising these layers exist independently of each other.
Layer 1 — Cell-Level Locking
Every cell in Excel has a property called Locked. By default, every cell in every new workbook is already marked as Locked. That sounds like it should mean something — but it doesn’t, not yet.
The Locked property does absolutely nothing on its own. It only activates when you switch on sheet protection. Think of it like deadbolts pre-installed on every door in a building — none of them engage until someone flips the master switch.
This is why the first step before protecting any sheet is deciding which cells should stay editable, and manually unlocking them. Skip this and you’ll lock everything — including your own input cells.
Layer 2 — Sheet Protection
Sheet protection is the switch that activates those deadbolts. Once it’s on, any cell marked as Locked cannot be edited without the password. It also gives you a checklist of other actions to allow or block — inserting rows, using AutoFilter, formatting cells, and more.
One important thing sheet protection does not do: it doesn’t affect the workbook structure at all. Even with every sheet individually protected, users can still add sheets, delete them, and rename them freely.
Layer 3 — Workbook Structure Protection
This layer locks down the workbook itself — the tabs, the structure, the architecture. With this enabled, users cannot add sheets, delete them, rename them, move them, or unhide any sheets you’ve hidden. If you’re distributing a dashboard to a client and don’t want them pulling it apart, this is the layer you need. It’s separate from sheet protection, and most people never turn it on.
Step 1 — Lock and Unlock Cells Before You Do Anything Else
This is the step most tutorials bury at the end or skip entirely — and it’s the reason so many people end up with a frozen, unusable sheet after their first attempt at protection. Do this before anything else.
How to Unlock Specific Cells for Editing
Since every cell starts as Locked, your job is to go through and unlock the ones you actually want people to edit — the input fields, the yellow boxes, the cells where data entry happens.
How to Unlock Input Cells
- Select the cells you want users to be able to edit after protection is on.
- Right-click and choose Format Cells, or press Ctrl + 1.
- Click the Protection tab.
- Uncheck the box labelled Locked.
- Click OK.
Why This Step Comes First
Think of it like laminating a document. Once the laminate goes on, only the windows you cut out in advance let anything through. If you forget to cut the windows before you laminate, the document is sealed. Cut the windows first. Then laminate.
💡 Pro Tip
If you have a large sheet with formula cells scattered throughout, press Ctrl + A to select everything and unlock all cells at once. Then re-lock just the formula cells. To find every formula cell instantly, press Ctrl + G → click Special → select Formulas → click OK. Excel highlights every formula cell. Lock those, leave the rest unlocked, then protect the sheet.
How to Protect Sheets and Workbooks in Excel — Step by Step
Protecting a Sheet with a Password
Protect Sheet — Steps
- Go to the Review tab on the ribbon.
- Click Protect Sheet.
- A dialog box appears with a checklist. Adjust the options based on what you need.
- Enter a password at the top if you want one. Without a password, anyone can remove protection in two clicks.
- Click OK.
- If you entered a password, confirm it when prompted and click OK again.
What the Protection Options Actually Mean
Most people leave the checklist on defaults without reading it. It’s worth a quick look.
Protecting the Workbook Structure
- Go to the Review tab.
- Click Protect Workbook.
- Make sure Structure is checked.
- Add a password if needed, then click OK.
⚠️ Common Mistake
Confusing Protect Sheet and Protect Workbook is extremely common. Protect Sheet controls cell content — editing, formatting, row insertion. Protect Workbook controls the file structure — tabs, sheet visibility, layout. You very often need both. A sheet-protected file with no workbook protection can still have its entire sheets deleted.
Excel Allow Edit Ranges — The Feature Most Users Never Touch
Here’s the thing — Allow Edit Ranges is arguably the most useful protection feature in Excel for real workplace scenarios, and it gets almost no attention in tutorials. It lets you define specific sections of a protected sheet that remain editable, with optional individual passwords for each section.
What Allow Edit Ranges Does
Instead of a sheet being either fully editable or fully locked, this feature lets you get precise. You can say: this team can edit columns B through D, that team can edit column F, and nobody touches the rest. You can even assign different passwords to different ranges.
How to Set It Up
- Before protecting the sheet, go to the Review tab.
- Click Allow Edit Ranges (in Excel 365 this may appear as Allow Users to Edit Ranges).
- Click New.
- Give the range a descriptive title for your reference.
- In the Refers to cells field, enter or select the range.
- Optionally enter a range password. Leave blank if you want anyone to edit that range without a password.
- Click OK. Repeat for any additional ranges.
- Then click Protect Sheet to activate everything.
When to Use It Instead of Full Sheet Protection
Use this when multiple people need to edit different parts of the same sheet — like a monthly reporting template where each department fills in their own section, but nobody should touch the instructions, totals, or another team’s section.
If you want to control what values users can enter into those editable ranges, the XplorExcel lesson on Data Validation covers exactly that — and it pairs naturally with Allow Edit Ranges.
How to Password Protect an Excel File — Actual Encryption
Everything covered so far — sheet protection, workbook structure protection, Allow Edit Ranges — is not encryption. It is access control within an already-open file. Trust me on this: it’s a distinction that matters.
Sheet protection and workbook structure protection are designed to prevent accidents. A determined person who knows what they’re doing can remove both without knowing the password. File-level password protection is a completely different mechanism.
File-Level Password vs Sheet Password — Key Differences
How to Encrypt an Excel File with a Password
- Go to File in the top left corner.
- Select Info from the left panel.
- Click Protect Workbook — note this is different from the one on the Review tab.
- Choose Encrypt with Password.
- Enter a strong password and click OK.
- Confirm the password and click OK.
- Save the file.
💡 Pro Tip
Use file encryption for genuinely sensitive data — payroll files, personal records, confidential reports. Use sheet and workbook protection for everything else — shared templates, dashboards, and forms where the goal is preventing accidents rather than controlling access.
The Security Caveat You Need to Know
Almost no tutorial mentions this. It’s worth knowing before you rely on protection for anything important.
Sheet Protection Is Not Encryption
Excel sheet protection is designed to prevent accidents, not to stop a motivated person. Someone with the right knowledge can remove sheet and workbook protection without knowing the password. Microsoft is transparent about this — the feature exists to prevent mistakes, not to serve as a security layer.
When Excel Protection Is Enough — And When It Isn’t
It is enough when you want to stop a colleague accidentally deleting a formula, prevent users from renaming sheets in a shared dashboard, or guide people to fill in only the correct fields in a template.
It is not enough when you’re working with genuinely sensitive data — personal information, financial records, anything confidential — that should never be seen by unauthorised users. For that, you need file-level encryption as a minimum. Microsoft’s official documentation on protection and security in Excel is worth bookmarking — it covers the technical specifications behind Excel’s encryption implementation.
How to Unprotect a Sheet or Workbook
Removing Protection When You Know the Password
- Go to the Review tab.
- Click Unprotect Sheet or Unprotect Workbook.
- Enter the password when prompted.
- Click OK. Protection is removed immediately.
What to Do If You Forget the Password
This happens more often than people admit. Excel has no built-in password recovery tool for sheet protection. Your options are third-party recovery tools — which vary a lot in quality — or, in the worst case, rebuilding the file.
The real fix is prevention. Store your Excel passwords in a password manager. Contextures has a detailed breakdown of how protection behaves across different Excel versions — useful if your team works across older and newer Excel releases.
Common Mistakes and How to Avoid Them
⚠️ Common Mistake
Protecting the sheet before unlocking input cells. The result is a completely frozen sheet where nobody can enter anything. Always unlock editable cells first.
⚠️ Common Mistake
Using the same password for every protected sheet and file. If one gets shared or guessed, everything is exposed. Use different passwords, especially for sensitive files.
⚠️ Common Mistake
Relying on sheet protection to secure sensitive data. If the information genuinely should not be seen by unauthorised users, sheet protection alone is not sufficient. Encrypt the file.
⚠️ Common Mistake
Forgetting workbook structure protection after protecting individual sheets. Users can still delete entire sheets even if every cell inside them is locked.
⚠️ Common Mistake
Losing track of which cells are unlocked in complex templates. Use a colour convention — many teams mark editable cells in yellow — so the structure is visible at a glance.
Quick Reference — Which Protection Should You Use?
For a practical walkthrough of setting up a file for team use from the ground up, the XplorExcel lesson on sharing and collaborating in Excel picks up right where this one leaves off.
Keyboard Shortcuts — Protection
Ctrl + 1 → Open Format Cells dialog
Ctrl + A → Select all cells
Ctrl + G → Special → Go to formula cells
Review tab → All protection options
🧪 Try It Yourself
This exercise takes about ten minutes and leaves you with a real working protected template.
- Open a blank Excel workbook.
- In A1 through A5, type these labels: Name, Department, Date, Hours Worked, Notes.
- Leave B1 through B5 blank — these will be your editable input cells.
- In B6, type this formula: =SUM(B4:B4) as a placeholder to protect.
- Press Ctrl + A, then Ctrl + 1 → Protection tab → uncheck Locked → OK. Everything is now unlocked.
- Select just B6 → Ctrl + 1 → Protection tab → check Locked → OK. That formula is now locked.
- Go to Review → Protect Sheet. Keep the defaults, optionally add a password, click OK.
- Try typing in B1 through B5 — you should be able to edit freely.
- Try clicking on B6 and typing — you should see the protection warning.
- Go to Review → Protect Workbook. Check Structure, add a password, click OK.
- Right-click any sheet tab — the options to insert, delete, and rename should be greyed out.
You’ve just built a protected data entry form with a locked formula and a secured workbook structure — the exact pattern behind most professional Excel templates used in real workplaces every day.
📚 External Resources
Microsoft Official Documentation
Technical specifications for Excel’s encryption and protection features, straight from the source.
Protection and Security in Excel → Microsoft SupportContextures — Debra Dalgleish
One of the most thorough independent resources on Excel workbook protection, including how behaviour differs across Excel versions.
Protect Excel Workbook → ContexturesLesson No. 25 : Protect Sheets and Workbooks in ExcelSECONDARY / LSI KEYWORDS: Excel password protect sheet | Excel lock cells | Excel workbook protection | Excel allow edit ranges | protect Excel file OG TITLE (RankMath > Social tab): Protect Sheets and Workbooks in Excel — Lock Down Your Spreadsheets OG DESCRIPTION: Learn how to protect sheets and workbooks in Excel. Lock cells, password protect worksheets, and secure your Excel files from unwanted changes. Free guide. OG IMAGE URL: https://xplorexcel.com/wp-content/uploads/lesson-25-protect-sheets-and-workbooks-in-excel.jpg TWITTER CARD: summary_large_image TWITTER TITLE: Protect Sheets and Workbooks in Excel: Full Guide | XplorExcel TWITTER DESCRIPTION: Learn how to protect sheets and workbooks in Excel. Lock cells,