Learn how Excel's two-layer protection system actually works — and how to use it to lock formula cells, hide proprietary logic, and grant granular edit access to specific users or ranges. This is the lesson that turns a fragile shared workbook into a hardened, professional tool.
You've spent three days building a quarterly budget model. It has layered formulas, carefully structured input cells, a validation system that enforces data entry rules, and a summary dashboard that leadership will use every week. You send it to the team. By Thursday morning, someone has accidentally deleted a SUMIFS formula in the assumptions tab, another person has typed over a drop-down validation cell with raw text, and a third has restructured the column layout entirely. The model is broken, and you're the one who has to fix it.
This scenario plays out constantly in organizations that rely on shared Excel workbooks. The problem isn't that your colleagues are careless — it's that Excel's default behavior allows anyone to change anything, anywhere, at any time. Workbook and cell protection is the feature set that closes this gap. When used well, it creates a clear distinction between cells users are supposed to interact with and cells they shouldn't touch, and it does so in a way that's transparent and professional.
By the end of this lesson, you'll know exactly how Excel's two-layer protection system works, how to selectively lock and unlock ranges, how to hide the formulas that power your models, and how to configure per-user edit permissions for high-stakes shared files. You'll also understand the real limits of this system — because knowing what protection can't do is just as important as knowing what it can.
What you'll learn:
You should be comfortable navigating Excel's ribbon and dialog boxes. Familiarity with cell references and absolute/mixed addressing will help when selecting ranges for protection. If you've worked with data validation and drop-down lists, you already understand the concept of constraining what users can do in a cell — protection extends that idea to the structural level.
Most people approach cell protection backwards. They select a range, look for a "lock this cell" button, and get confused when they can't find one — or when they do find it, it doesn't seem to do anything. Understanding the architecture first will save you a lot of frustration.
Excel's protection operates as a two-layer system:
Layer 1 — The Locked Property: Every cell in every worksheet has a "Locked" property. By default, this property is set to TRUE for all cells in every new workbook. But here's the critical part: this property does absolutely nothing until you activate Layer 2.
Layer 2 — Worksheet Protection: When you protect a sheet via Review > Protect Sheet, Excel looks at each cell's Locked property and enforces it. Locked cells become read-only. Unlocked cells remain editable. Until you turn on sheet protection, the Locked property is completely inert.
This two-layer design is intentional and powerful. It means the typical workflow is:
It's a subtractive model: start with everything locked, then carve out the editable spaces. This is usually safer than trying to lock specific cells after the fact, because it's easy to miss a formula cell when you're selecting individual ranges.
Key insight
The common mistake is selecting formula cells and trying to "lock" them, not realizing they're already locked by default. The real work is unlocking your input cells before you activate protection.
Let's work through a realistic example. Imagine you have a project cost tracking workbook with a sheet called "Budget Input." The structure looks like this:
=B2-C2)=D2/B2)You want finance to be able to edit Column B, project managers to edit Column C, but nobody should touch Columns D or E (formulas), Column A (labels), or the header row.
Start by selecting all cells and unlocking them all — then re-lock just the formula cells:
Actually, let's use the cleaner subtractive approach:
Step 1a: Select the entire sheet
Click the gray triangle at the intersection of the row and column headers (top-left corner of the grid) to select all cells.
Step 1b: Open Format Cells and confirm they're all locked
Right-click any cell, choose Format Cells, and go to the Protection tab. You'll see "Locked" is checked. This is the default state — every cell is locked. Click Cancel without changing anything.
Step 1c: Select your input cells and unlock them
Hold Ctrl and click to select B2:B20 and C2:C20 together. Now right-click and choose Format Cells > Protection tab. Uncheck "Locked" and click OK.
These cells are now unlocked. Column D and E formulas, Column A labels, and the header row are all still in their default locked state. When you protect the sheet, only B2:C20 will be editable.
Tip
You can also reach the Format Cells Protection tab through the ribbon: Home tab > Format (in the Cells group) > Format Cells > Protection. If you're working with named ranges, selecting them by name via the Name Box before unlocking can be faster and less error-prone than manual selection — see Named Ranges and Structured References for how to set those up.
With your input cells unlocked, you're ready to activate the protection layer.
Go to the Review tab on the ribbon and click Protect Sheet. A dialog box opens with these key options:
Password to unprotect sheet: This is optional but important for shared files. Without a password, any user can turn off protection themselves through the same Review tab. With a password, only people who know it can unprotect the sheet. More on password strategy below.
Allow all users of this worksheet to: This checkbox list controls what actions are permitted even in locked cells. The defaults allow users to select any cell (locked or unlocked). You can tune this:
For the budget tracking example, leave the defaults (only the two "select" options checked), optionally add a password, and click OK.
Warning
If you forget a worksheet protection password, Microsoft provides no recovery mechanism. The only workarounds involve third-party tools. Always store your protection passwords somewhere safe — a password manager is the right choice here, not a note in the same workbook.
Protecting cells prevents edits, but users can still click on a formula cell and read the full formula in the formula bar. For many situations this is fine — even desirable, since transparency helps users trust the model. But sometimes you need to obscure the logic: proprietary calculation methods, complex pricing models, IP you don't want copied.
This is what the Hidden property does. It's on the same Format Cells > Protection tab as the Locked property. When a cell is marked Hidden and the sheet is protected, clicking on that cell shows the value in the cell but displays nothing in the formula bar. The formula is invisible.
How to hide formulas:
Step 3a: Select the formula cells you want to hide (D2:E20 in our example).
Step 3b: Right-click > Format Cells > Protection tab. Check both "Locked" (should already be checked) and "Hidden." Click OK.
Step 3c: Make sure sheet protection is active (or activate it now). The Hidden property, like Locked, only takes effect when the sheet is protected.
After protection is on, click on cell D2. The cell shows the variance value. The formula bar is empty. A user reading the sheet has no idea whether that value comes from a formula or was typed manually.
Note
The Hidden property only hides the formula from the formula bar. It does not hide the value in the cell. If you want to hide the value entirely, you'd use custom number formatting (formatting as ;;;) or white font color — but those techniques are a different tool for a different problem, and they don't constitute real security either.
Standard sheet protection is binary: either you know the password and can edit everything, or you don't and can only edit the unlocked cells. For many scenarios that's sufficient. But enterprise environments often need something more granular: finance edits Column B, project managers edit Column C, and neither group can touch the other's range.
Excel handles this with the Allow Users to Edit Ranges feature, which must be configured before you protect the sheet.
Setting it up:
Go to Review tab > Allow Users to Edit Ranges (in Excel for Windows; on Mac this feature is more limited). The dialog shows any already-configured ranges. Click New to create one.
In the "New Range" dialog:
=$B$2:$B$20Tip
In a corporate environment joined to Active Directory or Azure AD, you can click the Permissions button in the New Range dialog to specify Windows user accounts or security groups who can edit this range without needing any password at all. This is far more maintainable than managing shared passwords — it ties access to the same credentials users already log in with.
Create a second range for "Actual Spend" covering =$C$2:$C$20 with its own password or permission set.
After configuring your ranges, protect the sheet normally. Now:
Everything we've covered so far operates at the worksheet level. But there's a second, separate protection layer: workbook protection, which governs the structure of the workbook itself rather than the content of cells.
Workbook protection prevents users from:
This matters more than people realize. If your budget model has a carefully organized tab structure — an Assumptions tab, a Calculations tab, a Dashboard tab, and a hidden Reference Data tab — workbook protection ensures that structure stays intact. Without it, users can accidentally (or intentionally) delete the Reference Data tab and break every lookup that depends on it.
Activating workbook protection:
Review tab > Protect Workbook. A simple dialog asks for an optional password and confirms you're protecting Structure (and optionally Windows, which controls window sizing — generally less relevant today). Click OK.
Note
Workbook protection and worksheet protection are completely independent. You can have one without the other. In practice, for a production shared workbook, you'll typically want both: worksheet protection to control cell-level edits, and workbook protection to prevent structural changes.
Let's put everything together in a complete, realistic scenario. You're setting up a monthly P&L reporting workbook that will be shared with department heads. The workbook has three sheets:
The Data Entry sheet has:
=B2-C2 (locked and hidden)Step 1: Select the entire Data Entry sheet (Ctrl+A).
Step 2: Format Cells > Protection > confirm Locked is checked > OK (no change, just confirming the default).
Step 3: Select B2:C13. Format Cells > Protection > uncheck Locked > OK.
Step 4: Select D2:D13. Format Cells > Protection > confirm Locked is checked, and also check Hidden > OK.
Step 5: Review > Allow Users to Edit Ranges > New. Title: "Revenue Input", Refers to: =$B$2:$B$13, set appropriate password or permissions. Create a second range for "Expense Input" covering C2:C13.
Step 6: Review > Protect Sheet. Choose a strong password. In the permissions list, also check "Use AutoFilter" if the sheet uses filters. OK.
This sheet should be completely off-limits.
Step 1: All cells are already locked by default — no unlocking needed.
Step 2: Select all cells (Ctrl+A). Format Cells > Protection > check both Locked and Hidden > OK. This hides all formulas on the sheet.
Step 3: Review > Protect Sheet. Strong password. In the permissions list, uncheck "Select locked cells" — this means users can't even click on cells in this sheet. OK.
Key insight
Unchecking "Select locked cells" in the Protect Sheet dialog creates a sheet that users literally cannot interact with. Their cursor will jump to the next unlocked cell (there are none), making the sheet feel read-only at a deeper level than usual. This is appropriate for intermediate calculation sheets that users have no reason to examine.
The dashboard is read-only but users need to be able to read and navigate it freely.
Step 1: All cells locked by default — no changes needed.
Step 2: Review > Protect Sheet. Password optional here (since you're not hiding anything, just preventing edits). Leave "Select locked cells" checked so users can navigate and copy values. OK.
Finally: Review > Protect Workbook. Set a password (can be different from your sheet passwords). Click OK.
Now the workbook is fully hardened. The tab structure is frozen, the Calculations sheet is a black box, the Dashboard is read-only, and only users with the right credentials can update the Data Entry sheet's specific ranges.
Almost always this means you protected the sheet without first unlocking your input cells — but actually the reverse. More likely: you unlocked cells that you thought were formula cells, then protected the sheet, and now the "wrong" cells are editable.
To diagnose: unprotect the sheet temporarily. Go to Home > Find & Select > Go To Special > select "Formulas." This selects every formula cell in the sheet. Then check the Protection tab for those cells. Any that show Locked unchecked are your problem — lock them, re-protect.
Tip
Go To Special is an essential tool for auditing protection configurations. Use "Go To Special > Constants" to select all hardcoded value cells, and "Go To Special > Formulas" to select all formula cells, so you can apply locked/unlocked status systematically rather than manually.
The Hidden property only activates when the sheet is protected. If you checked Hidden but haven't protected the sheet yet, the formula bar will still show formulas. Protect the sheet and check again.
Sheet protection, by default, disables sorting and filtering. Unprotect the sheet, go to Review > Protect Sheet, and check "Sort" and "Use AutoFilter" in the permissions list before re-protecting.
There's no built-in recovery. Third-party tools like PassFab for Excel or similar utilities can crack simple passwords through brute force. This is worth knowing both as a recovery option and as a security consideration: Excel's worksheet protection is not cryptographically strong. It's an integrity control, not a security barrier.
Neither conditional formatting nor data validation is affected by protection directly, but some users inadvertently check "Format cells" as OFF in the Protect Sheet permissions, which can prevent certain formatting operations from firing. More commonly, if users have the ability to delete rows, they can delete the validation rules with the row. Lock down row deletion in your Protect Sheet settings.
Warning
Excel protection is not a substitute for genuine data security. A determined user with technical skills can extract data from an Excel file using Power Query, Python's openpyxl library, or simply by opening the file's XML structure (Excel files are ZIP archives). If the data itself is sensitive, use proper access controls at the file system or cloud platform level. Excel protection is for preventing accidental changes, not for preventing intentional data extraction.
When you copy a sheet to another workbook, the protection settings — including the password — travel with it. You'll need to unprotect the sheet in the destination workbook if you need to modify it there. This is actually a useful feature: protection isn't stripped when the file moves around.
These functions build dynamic references, and if their referenced cells are locked, the references themselves work fine — protection doesn't prevent formulas from reading locked cells, only from writing to them. If you're seeing calculation issues, check whether the issue is related to automatic vs. manual calculation mode rather than protection. Exploring how OFFSET and INDIRECT interact with dynamic named ranges may also help you design structures that work cleanly in protected environments.
Build a protected timesheet workbook with these specifications:
Sheet 1: "Time Entry"
Set up the following columns:
=C2+(D2*1.5) (locked and hidden)=E2*$H$1 (locked and hidden, references an hourly rate in H1)Your tasks:
Verification checklist:
It's worth briefly covering how protection interacts with features you may already be using.
PivotTables: If a PivotTable sits on a protected sheet, users won't be able to interact with it unless you check "Use PivotTable and PivotChart" in the Protect Sheet permissions. If you're building interactive dashboards with Pivot Tables, you'll need to explicitly enable this.
Data Validation: Data validation rules survive protection and continue to work normally. In fact, the combination is powerful: data validation constrains what users can enter, while protection constrains where they can enter it. If you've followed the techniques in Master Data Validation and Drop-Down Lists, your drop-downs and input messages will still function correctly on protected sheets.
Conditional Formatting: Conditional formatting evaluates and displays normally on protected sheets. Users can't change the rules (since Format Cells is restricted by default), but the formatting renders correctly. This means a status-highlighting system built with conditional formatting techniques continues to work exactly as designed even after protection is enabled.
Macros and VBA: VBA code can interact with protected sheets in two ways. Code running without a specific user context can unprotect and re-protect sheets programmatically using ActiveSheet.Unprotect Password:="yourpassword". Alternatively, you can protect sheets in VBA using UserInterfaceOnly:=True, which means protection only applies to manual user interactions — VBA can still modify locked cells freely. This is useful for building automated workflows on top of protected templates.
Excel Tables (ListObjects): Tables have some interesting behaviors under protection. You can allow users to insert rows in a table by checking "Insert rows" in Protect Sheet permissions. Structured references in table formulas continue to work on protected sheets, making Excel Tables with structured references a natural complement to protection in well-structured workbooks.
Note
Microsoft 365 has been gradually improving co-authoring behavior in protected workbooks. In older Excel versions, protection and real-time co-authoring could conflict. In current versions of Excel for Microsoft 365, this interaction is much smoother, but it's worth testing your specific configuration if you're using SharePoint or OneDrive-based co-authoring.
Not all workbooks need the same protection approach. Here's a practical framework:
Template workbooks (filled out fresh each time): Protect everything except input cells. Use the Hidden property on formulas. Lock the workbook structure. This is the full hardening approach covered throughout this lesson.
Reference/lookup workbooks (read-only data sources): Full sheet protection with "Select locked cells" checked so users can read and copy data, but can't modify anything. Workbook protection to prevent structural changes. No unlocked input cells needed.
Dashboard workbooks (display only): Full protection everywhere. Consider using the UserInterfaceOnly VBA protection approach if a refresh macro needs to update the dashboard automatically.
Collaborative input workbooks (multiple people entering data simultaneously): Minimal protection — just lock your formula cells and structural elements. Use Allow Users to Edit Ranges to create clear ownership zones. Don't lock users out of seeing formulas (it breeds distrust and makes troubleshooting hard).
Audit-trail workbooks (where you need to know who changed what): Excel's built-in protection doesn't provide an audit trail. For that, you need either the Track Changes feature (limited and being deprecated in newer versions) or a platform-level solution like SharePoint version history.
Excel's protection system is a discipline more than a feature. The key mental model to take away: Locked and Hidden are properties on cells that do nothing until sheet protection is activated. Sheet protection and workbook protection are separate layers that each address different concerns. And protection in Excel is an integrity control, not a security mechanism — it stops accidents, not determined bad actors.
The practical workflow is consistent: unlock input cells first, configure range-level permissions if needed, hide formulas on sensitive calculation sheets, protect sheets with appropriate passwords and permissions, then lock the workbook structure. Applied systematically, this transforms a fragile shared workbook into something that behaves like a real application.
From here, consider exploring how formula auditing tools can help you verify your protection logic before sharing a file — especially useful for confirming that formula cells are properly locked. And if you're building complex models where protection is only one layer of a larger integrity system, the techniques in named ranges and structured references make your workbooks significantly easier to protect systematically, since named ranges let you select logical groups of cells by name rather than hunting for ranges manually.