Lock Cells in Excel to Prevent Editing (Step-by-Step)
The CFO stopped mid-sentence. The number on the slide was wrong — not because the formula was broken, but because someone had typed directly into a calculation cell three days earlier and nobody caught it. I was 24. I learned more about model integrity in that meeting than in any training I've done since. Locking cells in Excel isn't a preference. It's the difference between a model that survives contact with other people and one that doesn't.
My former manager Paul Vasquez had a rule he called the Bus Test: if you get hit by a bus tomorrow, can someone else use your model without destroying it? Cell protection is a big part of that answer. This guide walks through exactly how to lock cells in Excel — which cells to lock, how to make the lock actually work, and where most people go wrong.
|
| The setup: input cells stay open for editing, formula cells get locked before the sheet is protected. |
What You Can Protect (and One Thing to Know Before You Start)
The goal is a sheet where some cells accept input and others are untouchable. A budget where the person using it can enter revenue figures but can't accidentally delete the formula that calculates gross margin. That's the setup.
Here's the counterintuitive part: in Microsoft Excel, every cell is marked "Locked" by default. That setting does nothing on its own. Worksheet protection is what activates it. Until you turn on Protect Sheet, the Locked checkbox in the Format Cells dialog is just a label with no effect. This trips up a lot of people — they check the box, nothing changes, and they assume they did something wrong. They didn't. They just stopped one step short.
On Excel for Mac, the steps are identical through the menus: Format Cells, Protection tab, then Review › Protect Sheet. The keyboard shortcut to open Format Cells is Ctrl+1 on Mac (or Cmd+1 on some versions). If you're on Microsoft 365, the interface matches regardless of platform.
Step 1: Decide Which Cells to Protect and Flip the Locked Property
Because every cell starts as Locked by default, the process runs backwards from what most people expect. You don't go find the formula cells and lock them. You start by unlocking everything, then re-lock only the cells that need protection. It's a two-pass approach, and skipping the first pass is the most common mistake I see.
I follow a color convention in every model I build: blue cells for user inputs, black for calculations. If you're already using that convention, locking is just the enforcement layer on top of it. Blue cells stay unlocked. Black and green cells get locked before the sheet is protected. The color tells you what to do.
Pass 1: Unlock the entire sheet first
- Press Ctrl+A to select the entire sheet.
- Open the Format Cells dialog with Ctrl+1.
- Go to the Protection tab and uncheck the Locked checkbox. Click OK. Every cell is now unlocked.
Pass 2: Re-lock only the cells you want protected
- Select only the cells you want to protect: your formula cells, headers, anything users shouldn't touch.
- Press Ctrl+1 again to open Format Cells.
- Go to the Protection tab and check Locked. Click OK.
Those cells are now flagged for protection. They won't actually be restricted yet — that happens in Step 2.
Want to lock formula cells without hunting them down manually? Go to Home › Find & Select › Formulas. Excel selects every formula cell on the sheet. Run Format Cells on that selection, check Locked, and you're done.
Step 2: Turn On Worksheet Protection to Make the Lock Stick
Once you've flagged the right cells as Locked, worksheet protection is what makes it real. Go to Review › Protect Sheet. A dialog opens with a password field and a list of checkboxes controlling what users can still do on a protected sheet: select cells, format rows, insert columns, and so on.
Setting a password (and what happens if you forget it)
The password field is optional. Without one, anyone can go to Review › Unprotect Sheet and undo everything in two clicks. For a model shared with colleagues who know Excel, that may be fine — protection against accidents, not against intent. For anything going to a client or a non-technical user, set a password.
Write the password down somewhere that isn't the workbook. Microsoft's documentation on worksheet protection is clear: Excel cannot recover a forgotten protect sheet password. There is no reset option. Set the password, store it separately.
Worksheet protection is not encryption. It reliably prevents accidental edits — someone pressing Delete in the wrong cell, or typing over a formula without realizing it. That's the real threat in most shared workbooks. For genuinely sensitive data, you need workbook-level encryption (File › Info › Protect Workbook), not just sheet protection.
Before you protect any sheet for distribution, test it. Enter zeros, negatives, blanks, and unrealistically large numbers in every unlocked input cell. Protect a sheet with an untested model and you've locked in whatever errors are already there. Every template I've published — including the three-statement model that's been downloaded over 40,000 times — goes through this before it ships.
Common Mistakes When Locking Cells in Excel (and How to Fix Them)
Three things catch most people.
The "nothing works" problem
You locked cells, protected the sheet, and users can still edit everything. Almost always this means you skipped the first pass — unlocking all cells before selectively re-locking. Because all cells start as Locked by default, protecting the sheet without that step locks everything, including the input cells users need. Go back to Step 1 and run both passes.
The forgotten password
Covered above, but worth repeating because it's unrecoverable. If you're locking cells in a shared Excel workbook and there's any chance you'll need to modify it later, store the password somewhere outside Excel before you protect the sheet.
Copy-paste bypassing protection
This one surprises people. A user can copy a cell from an unprotected sheet and paste it into a protected one, and in some configurations the paste will overwrite a locked cell. This is a known limitation. If it's a real concern for your use case, restrict clipboard paste in the Protect Sheet dialog checkboxes, or consider data validation rules as an additional layer.
Frequently Asked Questions
How do I lock specific cells in Excel without protecting the whole sheet?
You can't, technically — worksheet protection applies to the entire sheet, not to individual cells. What you control is which cells the protection affects. By unlocking your input cells first and then enabling Protect Sheet, you get the practical result: only the cells you flagged as Locked are restricted, and everything else stays editable. The sheet is protected, but most of it remains open.
Why can't I lock cells in Excel — nothing seems to work?
The most common cause is skipping the first pass: unlocking all cells before selectively re-locking the ones you want protected. Because every cell is Locked by default, protecting the sheet without that step locks everything — including input cells users need to edit. Start with Ctrl+A, open Format Cells, uncheck Locked on the entire sheet, then re-lock only your target cells before enabling Protect Sheet.
What happens if I forget my Excel sheet protection password?
Excel cannot recover it. There is no reset option, no support escalation that retrieves it — it's gone. Third-party tools exist that can remove worksheet protection, but they're not reliable across all Excel versions and aren't a workflow you want to depend on. Store the password outside the workbook before you protect the sheet.
How do I lock formula cells but still allow data entry in other cells?
Use Home › Find & Select › Formulas to automatically select every formula cell on the sheet, then mark those as Locked via Format Cells › Protection. Make sure your input cells are unlocked (Locked checkbox unchecked). Then enable Protect Sheet. The result is a sheet where users can enter data freely in input cells but can't touch your formulas. This is the standard setup for any shared financial model.
If you're building models other people will use, the foundational Excel habits matter as much as the advanced ones. Cell protection is one of them.
Join the conversation