Protecting an Excel Sheet Someone Else Will Type Into

A template is fine until it leaves your hands.

Then it goes to four people, one of them tabs into a total instead of a quantity, types the number they had in their head, and the formula is gone. The sheet still adds up. The column still looks like a column. Nothing is flagged, because nothing is wrong in any way Excel measures — a constant is a perfectly legal thing to put in a cell.

Look at CC-4315 in the grid below. Forty-six licences at 74 each is 3404. The cell says 3400. Somebody rounded it to the number on the budget line, and from that moment the row stopped responding to the two cells above it. The template outlived its formulas by about a week.

Sheet protection is the fix, and it is the most misunderstood feature in Excel — not because it is complicated, but because the first thing everybody tries does not work, and the reason why is never explained.

What this covers. Everything in sections 1 through 7 and 10 through 12 works in every version of Excel this century, including the web version with minor menu differences. ISFORMULA in section 9 needs Excel 2013 or later. LET needs 2021 or Microsoft 365. Nothing here needs VBA, though section 10 explains the one job that still does.

1) The Rule That Makes Everything Else Make Sense

Two facts, and almost nobody is told the second one:

1. Every cell in a new workbook is already Locked. Not most. Every one. Locked is the default state of the Locked checkbox in Format Cells.
2. Locked does absolutely nothing until you protect the sheet.

That is why the obvious approach fails. You select the formula column, tick Locked, and discover you can still type over it — because you have just re-applied a property it already had, on a sheet that is not enforcing that property.

So the actual job is the inverse of what it sounds like. You do not go around locking the cells you want to defend. You go around unlocking the few cells you want people to type into, and then protect the sheet. Everything you never touched stays locked, which was always the default and now finally means something.

Two steps, in this order:

Do them the other way round and you get a message box telling you the cell you are trying to change is on a protected sheet — which is Excel correctly doing the thing you asked for, one step too early.

2) Step One: Unlocking the Inputs Without Hunting for Them

On a nine-row grid you could select the input cells by eye. On a real model with fourteen tabs you cannot, and eyeballing it is exactly how one input cell gets left locked and a colleague spends twenty minutes believing the template is broken.

There is a better way, and it takes three keystrokes: let Excel find the constants for you.

🎯 Scenario: Make columns C and D typeable, and leave everything else — headers, labels, totals — locked.

1. Select the range people will work in, A1:F9.
2. Press F5 → Special → Constants, and untick Text so you are left with Numbers only.
3. Excel selects every hand-typed number in the range: C2:D9, plus E6 — the hardcode.
4. Ctrl+1 → Protection → untick Locked → OK.
5. Review → Protect Sheet → OK.

Step 3 is the point of doing it this way. Go To Special does not know which cells you meant to be inputs; it knows which cells are constants. E6 turning up inside a selection you expected to be C2:D9 is Excel telling you that a cell in your formula column is not a formula. Before you unlock anything, put the formula back:

E6:  =C6*D6      → 3404, and the 3400 is gone for good

Then repeat the selection. This is the whole audit, done for free, as a side effect of the fastest way to select inputs.

The same menu has the mirror image — F5 → Special → Formulas — which selects every formula cell. Both are worth binding to muscle memory. Between them they answer "which cells are typed, and which are calculated", which is the only question that matters when you are deciding what to lock.

The other route to the same checkbox: Home → Format → Lock Cell toggles Locked on the selection without opening a dialog. Excellent for one-off fixes, and it looks pressed-in when the selection is locked, which makes it a quick way to check a single cell's state.

3) Step Two: The Protect Sheet Dialog, Checkbox by Checkbox

Review → Protect Sheet opens a password box and a scrolling list of things to permit. Almost everybody clicks OK on the defaults. Two of those checkboxes are worth a moment's thought each time, and one of them is the single biggest usability win available here.

Turn off "Select locked cells". With it off, the cursor physically cannot land on a protected cell. Tab and Enter walk from one input to the next and skip everything else, so the sheet acquires a tab order — click the first input, then Tab, Tab, Tab all the way through. Users stop hitting the "cell is protected" message box entirely, because they never reach a protected cell. The warning box is not a helpful guardrail; it is a small failure every time it appears, and this checkbox removes the whole category.

The trade: you cannot select a locked cell to copy from it either, and neither can you. Unprotecting takes two clicks, so this costs you little and buys a lot.

The password is optional, and often should be left blank. A blank password still protects the sheet — anyone can lift it, but they must consciously choose to, via Review → Unprotect Sheet. That single deliberate action is enough to prevent the accident you are actually defending against. Put a password on only when you have a specific person in mind who should not be casually turning it off, and then read section 5 before you believe it stops them.

4) The Hidden Checkbox — Taking Formulas Out of the Formula Bar

The Protection tab has a second checkbox next to Locked: Hidden. Like Locked, it does nothing until the sheet is protected. Once it is:

• The cell still shows its result in the grid, exactly as before.
• The formula bar goes blank when you select it.
• Printing, charting, calculation, precedent tracing — all unaffected.

🎯 Scenario: The pricing logic in column E is commercially sensitive. Show the totals, hide the arithmetic.

1. Select E2:E9.
2. Ctrl+1 → Protection → tick Hidden, leave Locked ticked.
3. Review → Protect Sheet → and in this case leave Select locked cells on, or nobody can even click column E to admire the blankness.

Two honest limits. First, Hidden is a display setting on a protected sheet, and the previous sentence is the whole security model — the formula is sitting in the file in plain text, and section 5 explains how little stands between a curious person and it. Second, it hides the formula from the formula bar, not from the workbook: the show-formulas shortcut (Ctrl and the grave-accent key), a Trace Precedents arrow, or a fresh copy of the cell pasted into an unprotected sheet all give it up.

Use Hidden to reduce clutter and to signal "this is not yours to edit". Do not use it as the reason a competitor cannot see your margin calculation.

5) Say It Plainly: Sheet Protection Is Not Security

This section exists because people put sheet passwords on files they should have encrypted, and find out which one they needed at the worst possible time.

Worksheet and workbook-structure protection do not encrypt anything. The password is stored as a short hash whose only job is to stop the dialog reopening; the cell contents, the formulas, the hidden sheets and the "hidden" formulas all sit in the file as readable XML. Anyone who wants past it can get past it, in under a minute, without any specialist knowledge and without damaging the file. Search engines will not have to work hard to tell them how.

That is not a flaw. It is what the feature is for. Sheet protection is a seatbelt, not a vault — it exists so a colleague in a hurry cannot destroy a formula by accident, and it is excellent at that. Ninety-five percent of the damage done to spreadsheets is done by people with legitimate access, good intentions and a Tab key, and this stops all of it.

When you need the actual vault, there is exactly one place to go: File → Info → Protect Workbook → Encrypt with Password. That encrypts the file itself with AES. It is real cryptography, it is the same mechanism whether you are protecting a budget or a payroll run, and the corollary is the part people are not ready for: there is no recovery. No Microsoft support ticket, no admin override. Lose that password and the file is gone.

6) Three Different Locks, Often Confused

The word "protect" appears on three different commands that defend three different things. Mixing them up is why someone can still delete the whole sheet you carefully protected.

Protect Sheet (Review tab) — cell-level, one sheet at a time. Stops edits to locked cells on that sheet. Does nothing about the sheet itself: with only this on, a user can right-click the tab and delete every protected cell in one action.

Protect Workbook (Review tab, "Protect Workbook" → Structure) — tab-level, whole file. Stops sheets being inserted, deleted, renamed, moved, copied or unhidden. Says nothing about cell contents. This is the one that makes a hidden lookup tab actually stay hidden, and the one people forget.

Encrypt with Password (File → Info) — file-level. Nothing opens without the password.

They compose, and a distributed template usually wants the first two together: unlock the inputs and protect each sheet, then protect the workbook structure so the tabs survive. Thirty seconds of work that turns "please don't break this" in a covering email into something the file enforces on its own.

7) Allow Edit Ranges — Different People, Different Areas

Sometimes one protection setting is too blunt: the department heads should each fill in their own block, and not each other's.

Review → Allow Edit Ranges (available before you protect the sheet) lets you carve out named ranges that stay editable, each with its own password:

1. New, give the range a title such as CC-4100 Block, point it at A2:F3.
2. Give that range a password — different from the sheet password.
3. Repeat per block. Then Protect Sheet.

Now the sheet refuses edits everywhere, but a user who selects a cell inside CC-4100 Block gets a prompt for that block's password and, once given, can edit it for the rest of the session. On a Windows domain there is also a Permissions button that grants the range to named accounts, so the right people never see a prompt at all.

This is genuinely useful for a shared planning sheet, with one caveat that follows straight from section 5: the range passwords are exactly as breakable as the sheet password. Use it to keep well-behaved people in their own lane. Do not use it to keep salary bands from the people whose bands they are.

8) Protection Stops Overwriting. Validation Stops Wrong Values.

Protection is a very literal guard. It has an opinion about which cells may change and no opinion at all about what may be put in them. Look at the last two rows of the example:

• CC-4408 was keyed with -2 units. Perfectly legal number, in a cell that is supposed to be typed into. Protection waves it through, and the line total is now -110.
• CC-4100 asks for the same 14in laptop refresh on row 2 and again on row 8. Two legal rows. Protection has nothing to say.

Neither of those is an overwrite, so protection is the wrong tool. Data → Data Validation is the right one, and the two are designed to be used together: validation says what is acceptable, protection makes sure nobody can rip the validation out.

🎯 Scenario: Units must be a whole number above zero. Unit cost must be a non-negative number. And the same line item must not be requested twice for one cost centre.

Select C2:C9 → Data → Data Validation → Allow: Custom:

=AND(ISNUMBER(C2), C2>0, C2=INT(C2))

Select D2:D9, same route:

=AND(ISNUMBER(D2), D2>=0)

Select B2:B9, same route:

=COUNTIFS($A$2:$A$9, $A2, $B$2:$B$9, $B2) = 1

Three things about writing these that will save you an afternoon:

1. Write the formula relative to the active cell of the selection. With C2:C9 selected and C2 active, you write C2 and Excel fills the rest down for you. Write C5 by accident and every row is validated against row 5's neighbourhood.
2. The rule must return TRUE or FALSE. =ISNUMBER(C2) is a rule; =C2 is not, and Excel will accept it and then behave in ways you will not enjoy.
3. Anchor the ranges you are scanning, not the cell you are testing. $A$2:$A$9 stays put as the rule fills down; $A2 moves down with it. Get that backwards and the duplicate check silently only ever checks row 2.

Then set the Error Alert tab to Stop with a message that says what to do — "Units must be a whole number of 1 or more" beats "The value you entered is not valid", which is what an empty Error Alert tab gives you.

The gap that remains, stated honestly: data validation applies to typing, not to pasting. A user who copies a block from an email and pastes it into C2:C9 replaces the cells' rules along with their contents, and no dialog appears. There is no checkbox in Protect Sheet that closes this. Two practical mitigations:

• Data → Data Validation → Circle Invalid Data draws a red ring around every cell whose current value breaks its rule. Run it before you trust a returned template. It catches the -2 instantly.
• F5 → Special → Data validation → Same with a validated cell selected shows you every cell still carrying that rule. If the selection comes back smaller than the column, somebody pasted.

Validation and protection cover different halves of the problem, and a template that has one without the other has a hole in a predictable place.

9) Auditing the Sheet Before You Protect It

Protecting a sheet freezes whatever is in it — including a wrong number typed over a formula three revisions ago. Protection is not the moment to start checking; it is the moment you stop being able to fix things quietly. So audit first.

🎯 Scenario: Prove that every cell in E2:E9 really is a formula, before locking the column.

=SUMPRODUCT(--ISFORMULA(E2:E9))     → 7
=ROWS(E2:E9)                        → 8

Seven formulas in eight rows. That difference is the whole audit, and it fits in two cells you can leave permanently on a hidden sheet. Then name the culprit rather than going hunting:

=TEXTJOIN(", ", TRUE, IF(ISFORMULA(E2:E9), "", A2:A9 & " " & B2:B9))
                                    → CC-4315 Licence renewal (annual)

(In Excel 2019, commit that with Ctrl+Shift+Enter. In 2021 and 365 it just works.)

A version that reports on itself, so the check reads as a sentence in a cell:

=LET(
  col,    E2:E9,
  gaps,   ROWS(col) - SUMPRODUCT(--ISFORMULA(col)),
  IF(gaps = 0,
     "OK - all " & ROWS(col) & " rows calculated",
     gaps & " hardcoded value(s): " & TEXTJOIN(", ", TRUE, IF(ISFORMULA(col), "", A2:A9)))
)

Seeing the locked state, not inferring it. CELL("protect", ref) returns 1 for a locked cell and 0 for an unlocked one:

=CELL("protect", C2)     → 0 once you have unlocked the inputs
=CELL("protect", E2)     → 1

Turn that into a map of the whole sheet with conditional formatting. Select A1:F9, Home → Conditional Formatting → New Rule → Use a formula:

=CELL("protect", A1) = 0        → fill pale green: "you may type here"

And the companion rule, which is worth keeping in every model you build:

=ISFORMULA(A1)                  → fill pale grey: "this is calculated"

Two rules, and the sheet now explains itself in colour: green cells are yours, grey cells are the model's, and a green cell inside a grey column is a bug you can see from across the room — E6 lights up green before you have run a single check.

One caveat on CELL, because it will confuse you exactly once: locking a cell is a formatting change, and formatting changes do not trigger a recalculation. Toggle Locked and the colours will not move until something recalculates. Press Ctrl+Alt+F9 to force a full rebuild.

10) Seven Things Protection Quietly Breaks

Protection is enforced far more broadly than "typing into a cell", and the surprises all arrive after you have distributed the file. Each of these is worth knowing before, not after.

1. Sorting. "Sort" allowed in the dialog is necessary but not sufficient — every cell in the sorted range must also be unlocked, because sorting rewrites all of them. A sort across a block of locked labels fails no matter what the checkbox says.
2. AutoFilter. Ticking "Use AutoFilter" lets people use filter dropdowns that already existed when you protected the sheet. It does not let them add filtering to a sheet that had none. Apply the filter first, then protect.
3. Grouped rows and columns. Expanding and collapsing an outline is blocked, and there is no checkbox for it. The only fix is a line of VBA in the workbook's Open event: Sheet1.EnableOutlining = True together with Sheet1.Protect UserInterfaceOnly:=True.
4. Tables. Typing in the row below a Table normally extends it. On a protected sheet that fails, even with "Insert rows" allowed, because extending a Table writes to cells outside the unlocked area.
5. Spilled arrays. A dynamic array formula needs its whole spill range available, not just the cell it sits in. Unlock the entire area a result can grow into, and leave headroom for the month it returns three more rows than you planned.
6. Refreshing queries and pivots. A Power Query load or a PivotTable refresh writes to the sheet, so both fail on a protected one. "Use PivotTable & PivotChart" permits interaction with an existing pivot, not a data refresh. If the sheet must refresh on its own, that is the second legitimate job for VBA: unprotect, refresh, re-protect.
7. VBA in general. Sheet protection blocks macros too, and this is a feature — a macro that assumes it can write anywhere will fail on a protected sheet. Protect UserInterfaceOnly:=True is the answer: humans are blocked, code is not. It resets when the file is closed, so it belongs in Workbook_Open.

None of these are reasons not to protect a sheet. They are reasons to open the file once, as a user would, and click the things a user will click — before you send it.

11) The Ten-Minute Recipe

For a template that is about to be sent to people who did not build it:

1. Fix the model first. Run the ISFORMULA count from section 9 on every calculated column. Put back any formula somebody typed over.
2. Select the input cells with F5 → Special → Constants → Numbers, and check the selection is exactly what you expected. Anything unexpected in it is a finding.
3. Unlock them — Ctrl+1 → Protection → untick Locked.
4. Colour them with the two conditional formatting rules from section 9, so the sheet tells its own story.
5. Add validation to each input range, with a Stop alert that says what a good value looks like.
6. Tick Hidden on any formula range whose logic you would rather not display.
7. Protect Sheet, with "Select locked cells" off so Tab walks the inputs.
8. Protect Workbook structure, so the tabs cannot be deleted or unhidden.
9. Test it as a user. Open it fresh. Tab from the first input to the last. Try to break a formula. Try to sort. Try the filter.
10. Write the password down somewhere that is not the file. And if a lost password would be a genuine emergency, re-read section 5 and decide whether you wanted encryption instead.

12) Mini Exercises

Copy the grid into a blank sheet starting at A1. Every answer is a single formula or a short menu sequence.

1. Find the hardcode without looking. Write one formula that returns the cost centre and line item of every row in E2:E9 that is not a formula. It should return one row today, and nothing once you fix E6.
2. Count what you are about to lock. Before protecting, write a formula that returns how many cells in A1:F9 are currently unlocked. (CELL takes a single reference, not a range — so what do you use instead?)
3. A rule for the notes column. Column F should accept any text up to 60 characters, but not a number. Write it as a custom validation formula.
4. Catch the duplicate two ways. Row 8 repeats row 2's line item within the same cost centre. Write the COUNTIFS validation rule that blocks it on entry, then a separate audit formula that finds it in data already typed.
5. The negative that should be allowed. Suppose a credit line genuinely needs negative units, but only for cost centre CC-4408. Rewrite the units rule so it permits that one case and no others.
6. Explain a failure. You protect the sheet with "Sort" ticked, and sorting still fails. Give the reason in one sentence, and the fix in one more.

Summary

Protection is two steps that people do in one order and should do in the other. Every cell starts locked, so the job is to unlock the handful you want typed into, and only then turn protection on. Get that round the right way and the rest of the feature stops being mysterious.

The three habits worth keeping. Let Go To Special find your inputs, because the cell it turns up that you did not expect is the bug you were going to ship. Pair every protected sheet with validation, because protection has an opinion about which cells change and none at all about what goes in them. And be honest about which of the three locks you are reaching for — a sheet password is a seatbelt, and the only thing in Excel that behaves like a vault is on the File menu, where losing the password loses the file.

The template you send out is the last version you fully control. Everything after that is someone else's Tab key.