Locking cells in Excel means turning on worksheet protection after setting the cells' locked status — a two-stage process most people get wrong at stage two. If you only tick the "Locked" checkbox in Format Cells, nothing changes on screen; the lock only takes effect once you click Protect Sheet. This guide walks through the exact clicks in Microsoft 365 and Excel 2021, shows how to lock a single cell, a range, all cells except an input area, and unlocked formula cells, and flags the failure cases that make protection appear not to work.
What Cell Locking Actually Does in Excel
The Locked property is a formatting attribute, not an active security measure. Every cell in a new workbook ships with Locked already ticked in the Format Cells dialog — that's why protecting a sheet immediately blocks editing everywhere, and why your first attempt to lock one cell can appear to freeze the whole sheet. Excel stores this attribute per cell, alongside font and fill, so it travels with the workbook and survives copy-paste of cell formats. Microsoft's own support documentation for Excel protection confirms the sequence: set the Locked and Hidden checkboxes first, then apply sheet protection. Neither action does anything visible on its own. The screen looks identical after you tick Locked — no border, no color, no warning icon.
The protection password is optional, and that matters more than the dialog suggests. If you click Protect Sheet and press OK without typing a password, anyone can lift the protection through Review > Unprotect Sheet in about two clicks. A password raises the effort required but does not encrypt the file: Excel stores it as a hash, and commercially available password-recovery utilities can remove sheet protection from an unencrypted .xlsx file. Sheet protection is a guardrail against accidents and casual edits. For confidential data, Excel's file-level encryption under File > Info > Protect Workbook > Encrypt with Password is the tool that actually protects the contents.
Two behaviors surprise people. First, protection applies to the worksheet you are standing on, so a workbook with four tabs needs four Protect Sheet actions unless you protect them as a group by right-clicking a tab and choosing Select All Sheets. Second, the default Protect Sheet dialog allows selecting locked cells — users can click and read them, just not type. Unchecking "Select locked cells" makes cells unreachable by click, though the arrow keys and Name Box can still land on them until you also clear "Select unlocked cells".
One version note worth knowing before you start: Excel for the web opens and displays protected worksheets, but the Protect Sheet command lives in the Review tab there too, and behavior differs for editors using the browser versus the desktop app. On the desktop side, the Excel 2021 and Microsoft 365 ribbons are identical for this task. Mac Excel places Protect Sheet under the Review tab as well, but the Format Cells > Protection tab requires the Format Cells dialog rather than a shortcut, which trips up Windows-trained users switching machines.
Lock a Single Cell or Range With Protect Sheet
This is the sequence for the common case: you want one cell, or one block, to resist typing while the rest of the sheet stays editable. Total time is about 20 seconds once you know the path, and the two critical clicks are in step 3 and step 6. Skip either and the lock silently fails.
1: Select the cell or drag across the range you want to protect. 2: Press Ctrl+1 (Cmd+1 on Mac) to open Format Cells, then click the Protection tab. 3: Tick Locked. Leave Hidden alone unless you also want the formula bar to show nothing when the cell is selected. 4: Click OK. Nothing on screen changes — that is expected. 5: Select every cell that should remain editable, open Format Cells > Protection again, and untick Locked for that selection. 6: Go to Review > Protect Sheet, leave "Allow all users of this worksheet to" at its defaults (selecting locked and unlocked cells only), type a password if you want one, and click OK.
The failure handling matters as much as the steps. If you tick Locked, protect the sheet, and find the whole sheet is frozen, you skipped step 5 — Excel protected everything because every cell still carries the default Locked tick. Unprotect via Review > Unprotect Sheet, clear Locked on the cells that should stay open, and reprotect. If typing into the target cell still works after step 6, you protected a different sheet than the one you were editing; check the active tab name in the Protect Sheet dialog. And if a collaborator reports the file "won't let me type," confirm they aren't opening a read-only copy from email — protection and file-level read-only produce identical-looking frustration but need different fixes.
The Hidden checkbox deserves its own warning. It only hides formulas when sheet protection is on, and it hides the formula from the formula bar and from a double-click into the cell — the cell's displayed result stays visible. If you hide a formula and then share the workbook, anyone who unprotects the sheet sees the formula again, so Hidden is a speed bump for the curious, not a vault.
Lock Everything Except Input Cells: The Reverse Method
Locking a form or template means unlocking the handful of cells people should fill in, then protecting the sheet so the default Locked state covers the rest. The logic inverts: don't lock what you want protected — unlock what you want editable. A 12-field expense form built this way takes one Protect Sheet action to secure, versus individually locking the other 200-odd cells. Microsoft's Excel templates use the same pattern, and it's the method to teach anyone maintaining a shared tracker.
1: Select the input cells (hold Ctrl to grab non-adjacent cells). 2: Press Ctrl+1, open the Protection tab, and untick Locked. Click OK. 3: Optionally color the input cells (light yellow fill is the informal convention) so users can see where to type. 4: Go to Review > Protect Sheet, set a password, and click OK. 5: Test by typing in a locked cell (should fail with a "protected sheet" dialog) and an input cell (should accept entry).
This is where the version-specific difference bites: if the input cells sit in a Table (Insert > Table, Ctrl+T), Excel treats the table body as an expandable region, and new rows added to the table inherit the protection behavior of the row above. In Microsoft 365, inserting a table row below a protected row can produce inconsistent editability across the new row. The workaround is to unlock the entire table body range before protecting, or to keep the input area as a plain range rather than a formatted Table when protection is the priority.
The failure case to plan for: people paste into an input cell and overwrite its formatting, which can carry the Locked attribute from the source. Excel's Paste Options let you choose Values (V) to dodge this, and the "Keep format" paste can re-lock a cell you deliberately opened. If a user reports "the yellow box stopped working," the likely cause is a format-carrying paste, fixed by re-unlocking that cell and reprotecting.
Budget your setup time honestly. Unlocking 12 non-adjacent input cells and testing the protection takes roughly the same effort as locking the same number of cells directly — the reverse method wins not on keystrokes but on maintenance: adding a new input field later means one unlock action, not scanning the sheet for whatever should now be protected.
Keep Formulas Editable but Lock Their Results
The most common real-world request is "protect the calculations, let me type the inputs" — which is the reverse method with one extra rule: identical locked settings across the formula cells, with the input cells unlocked. The trap is that this setup breaks the moment someone inserts a row inside the calculated range, because Excel extends protection to the new row's cells, and the inserted row inherits Locked from its neighbors. A locked formula row that gains a locked blank row is not a bug; it's the default, and it means the protected range quietly grows.
Here's the working setup for a simple invoice: Column C holds quantity (input, unlocked), Column D holds unit price (input, unlocked), Column E holds amount (formula =C2*D2, locked). Unlock C2:D40, leave E locked, protect the sheet. Typing in E fails; typing in C or D recalculates E normally, because protection blocks entry, not calculation. That last point is the one people doubt — protected formula cells still update when their precedents change, and they still recalculate on every edit.
Row insertion is the documented failure scenario. If a user right-clicks row 15 and inserts a row, Excel inserts above and shoves formulas down, and the new row 15 inherits the locked formatting of row 14 — so the new quantity and price cells arrive locked, and the user can't fill the new invoice line. The fix is not to allow Insert Rows in the Protect Sheet options while locking formula cells, because that permission also lets users insert rows that shift formulas unpredictably. The cleaner fix: unlock a spare "buffer" row at the bottom of the input range so insertions inside the protected zone are less likely, or unprotect, insert, and reprotect when adding lines.
Hidden protects the formula text, not the result. Tick Hidden on the E column cells, protect the sheet, and clicking E2 shows nothing in the formula bar while the cell still displays its number. Anyone who unprotects the sheet — or who opens a copy without the password — sees the full formula immediately. For formulas containing pricing logic or confidential rates, apply file-level encryption instead of relying on Hidden.
One adjacent behavior worth stating: Excel Tables with calculated columns handle this differently from plain ranges. A calculated column's formula auto-fills new rows, but with sheet protection on, the fill can be blocked because the target cells are locked. Microsoft's guidance for structured references and tables recommends unlocking the table's data body range for entry columns and leaving calculated columns protected — and testing row addition after each protection change, because that's the operation most likely to surface the inconsistency.
Choose the Right Protection Level: Sheet, Workbook, and File
Three protection layers exist in Excel, and people routinely click the wrong one. Sheet protection locks cells on one tab. Workbook protection (Review > Protect Workbook) blocks structural changes — adding, deleting, hiding, or renaming sheets — but leaves every cell editable. File-level encryption (File > Info > Protect Workbook > Encrypt with Password) locks the document itself and is the only one of the three that resists opening. Choosing correctly takes ten seconds; choosing wrongly wastes an afternoon.
| Protection layer | Menu path | Blocks | Leaves editable | Password optional? | Typical use |
|---|---|---|---|---|---|
| Sheet protection | Review > Protect Sheet | Cell entry on locked cells, plus whatever options you uncheck | Unlocked cells, formatting if allowed | Yes — protection lifts with no password | Shared trackers, forms, formula sheets |
| Workbook structure | Review > Protect Workbook | Insert/delete/rename/hide sheets, move sheets | All cell content on every sheet | Yes | Keep tab order fixed |
| File encryption | File > Info > Protect Workbook > Encrypt with Password | Opening the file entirely | Nothing until password entered | No — password required to open | Confidential data, distribution |
| Read-only recommendation | File > Info > Protect Workbook > Mark as Final | Nothing technically; prompts "read-only" on open | Everything, once prompt is dismissed | No | Courtesy warning only |
The verdict splits by need. Locking cells for a shared budget tracker: sheet protection, password on, input cells unlocked. Stopping colleagues from reordering or deleting your summary tabs: workbook structure protection. Sending salary data to a manager: file encryption, because sheet and workbook protection are both removable by anyone with a copy of the file and a recovery utility. Mark as Final is a nudge, not a lock — Excel even lets the recipient click "Edit Anyway" — so use it only for draft versions you want people to read rather than change.
Fix the "Locked Cell Still Editable" Failure Cases
Six specific failures account for nearly all "the lock didn't work" complaints. Each has one diagnosis and one fix, and the diagnostic order runs from cheapest check to most expensive. Work through them in this sequence before assuming Excel is broken.
If the whole sheet is editable after protection: you protected without any locked cells, or you unprotected earlier and forgot. Check Review tab — the button reads "Unprotect Sheet" when protection is on, "Protect Sheet" when it's off. If the sheet is protected but your target cell still accepts typing: that cell's Locked box is unticked in Format Cells > Protection, usually because a paste carried format from an unlocked source. If protection vanishes after closing and reopening: someone unprotected and saved, or the file is being opened from a synced copy where a co-author's edit won — check version history. If macros fail with "method Protect of object Worksheet failed": the sheet is already protected, and the macro needs an Unprotect call first. If a shared workbook can't enable protection at all: Excel blocks sheet protection in legacy shared workbooks (Review > Share Workbook), so switch to co-authoring in OneDrive or SharePoint. If the password is lost: sheet protection on an unencrypted .xlsx can be removed by third-party utilities, while an encrypted file with a lost password is effectively unrecoverable — Microsoft's documentation states the company cannot retrieve or bypass forgotten file passwords.
The most expensive of these is the shared-workbook case because it costs a migration, not a click. Legacy Share Workbook mode predates co-authoring and is still used in some offices; it disables Protect Sheet entirely, so a team standardizing on protected input forms should move the file to OneDrive or SharePoint and co-author instead. That change touches file storage, not Excel settings.
One more failure deserves a test, not a guess: protected sheets and Excel Tables interact badly enough that you should verify editability after protection with a live typing test in both a locked and unlocked cell, and again after adding a table row. Thirty seconds of testing at build time saves a support ticket at distribution time.
Verify, Test, and Document Your Protection
Protection you haven't tested is a guess. Build a five-point test into your setup routine, and it takes under a minute per sheet. First, click a locked cell and type — expect the "The cell or chart you're trying to change is on a protected sheet" dialog. Second, type in an unlocked cell — expect acceptance and, if formulas reference it, a recalculated result. Third, double-click a Hidden formula cell — expect an empty formula bar. Fourth, insert a row at the bottom of your input area — expect it to inherit editability, not lock. Fifth, save, close, reopen, and repeat test one — expect protection to persist.
Documentation matters because protection is invisible. A note in cell A1 of the sheet ("Yellow cells are editable; everything else is locked. Contact [owner] for changes.") costs nothing and prevents the email thread where a colleague insists the file is broken. Password storage is the other half: keep the sheet password in a team password manager, because the difference between recoverable and unrecoverable is whether the file is encrypted, and it's easy to forget which files carry which lock.
For teams, keep a one-row-per-sheet register: sheet name, protection status, password holder, last verified date. Review it when the file changes hands. This is the unglamorous part that makes the locking actually stick — the mechanics take 20 seconds, the discipline takes a schedule. And a caution on automation: VBA's Worksheet.Protect method accepts the same options as the dialog, and recorded macros will replay your exact choices, including the no-password default, so audit recorded protection code before distributing a macro-enabled workbook.
FAQ
Why can I still type in cells after ticking Locked?
Because Locked alone does nothing — you haven't protected the sheet yet. Open Review > Protect Sheet and click OK. The Full Sheet is still editable until that second action, and the checkbox being ticked in Format Cells > Protection is only half the sequence. If the button says "Unprotect Sheet" instead, protection is already on and the cell you're typing in is unlocked.
Does locking cells in Excel stop people from deleting rows or copying data?
No, not by default — sheet protection blocks entry into locked cells but allows selection, copying, and (unless you clear the relevant Protect Sheet options) row and column deletion within the sheet. Uncheck "Delete rows," "Delete columns," and "Format cells" in the Protect Sheet dialog to block those actions. Copying locked cell contents is still permitted while "Select locked cells" remains ticked.
Can someone bypass a sheet password?
Yes, in practice — sheet protection on an unencrypted .xlsx file can be removed with third-party recovery utilities, and the password is stored as a hash rather than encryption. Protect Sheet guards against accidents and casual edits, not determined access. For data that must stay confidential, use File > Info > Protect Workbook > Encrypt with Password, which is a genuine encryption layer.
What happens to locked cells if I insert a row?
The new row inherits the formatting, including the Locked attribute, from the row above it. Insert a row inside a protected, locked region and the new cells arrive locked, so users can't fill them without unprotecting first. This is the most common silent failure in invoice and form templates — plan a buffer row and test insertion after every protection change.
How do I lock only formulas and leave everything else open?
Unlock every cell users should edit (Ctrl+1 > Protection > untick Locked), protect the sheet, then confirm the formula cells are locked. Protected formulas still recalculate when their input cells change — protection blocks typing into the formula cell, not the calculation. Add Hidden on the formula cells if you also want the formula bar to show nothing when they're selected.
Does protecting a sheet remove protection when I close the file?
No — protection saves with the workbook and persists on reopen, provided you save before closing. If protection disappears, the cause is a co-author unprotecting and saving, or a sync conflict resolving to an older unprotected version. Check File > Info > Version History to find who saved last.
Is the password on Protect Sheet the same as the file-open password?
No, they're independent. The Protect Sheet password limits edits on one worksheet; the file-open password (File > Info > Encrypt with Password) is required to open the workbook at all. You can have sheet protection without file encryption, which is the usual setup for shared trackers — and the reason a colleague can open the file and read every value while still being unable to type in locked cells.
Can I lock cells on Excel for the web?
Yes, the Review tab in Excel for the web includes Protect Sheet with the same Locked-checkbox prerequisite set through Format Cells. The options list is trimmed compared to the desktop app, and some interactions with Tables and co-authoring differ, so verify editability in a browser editing session before distributing a protected file to web users — a lock that behaves correctly on desktop can behave differently in the browser.