To lock cells in Excel you have to work backwards. Every cell is already marked as locked, and that flag does nothing until you protect the sheet. So: press Ctrl+A, Ctrl+1, and untick Locked on the Protection tab. Then select only the cells people should be able to edit, leave those unlocked, tick Locked on everything else, and finally go to Review › Protect Sheet.
Why the obvious approach doesn’t work
Almost everyone tries the same thing first: select the formulas they want to protect, tick Locked, protect the sheet — and discover the whole worksheet has become read-only.
The reason is that every cell in a new workbook already has Locked ticked. It’s the default state. Ticking it on your formulas changes nothing, because it was already on. Protecting the sheet then activates that flag everywhere at once.
So the job isn’t locking cells. It’s unlocking the handful of cells people should be able to type into, then protecting the sheet so everything else becomes read-only.
Locked is on by default and inert by default. Protect Sheet is the switch that gives it meaning.
Doing it properly
Starting with Ctrl+A and unticking Locked feels backwards but it’s the reliable route — you’re clearing the slate so you know exactly what state every cell is in. On an inherited spreadsheet where somebody has already been fiddling, this is the only way to be sure.
When selecting the cells people should edit, hold Ctrl to pick several separate ranges in one go. You can format all of them together, which saves working through a form field by field.
💡 Pro tip: Give your input cells a visible style — a pale yellow fill works well — before you protect. People shouldn’t have to click around discovering which cells accept typing.
The Protect Sheet options
The dialog that appears offers a long list of checkboxes, and the defaults are sensible for most cases. Three are worth understanding.
Untick ‘Select locked cells’ if you don’t want people clicking into formula cells at all. The cursor then skips over them entirely and Tab moves cleanly between input fields — which makes a data-entry form feel considerably more polished.
If users need to add rows to a table, tick Insert rows. Forgetting this is the most common complaint about a protected sheet: everything works until someone needs to add a record.
The password is not security
Protect Sheet offers a password. It’s worth being clear about what that password does, because a lot of people assume more than it delivers.
Sheet protection passwords are trivially removable. Free tools do it in seconds, and there are well-known manual methods involving renaming the file to .zip and editing the XML inside. It is not encryption and Microsoft has never claimed it was.
What it’s genuinely for is preventing accidents. It stops a colleague overwriting a formula by pasting into the wrong cell. It keeps a template’s structure intact as it circulates. Those are real, valuable problems and sheet protection solves them well.
⚠️ Watch out: If the data is genuinely confidential, use File › Info › Protect Workbook › Encrypt with Password instead. That encrypts the entire file and cannot be bypassed. Losing that password means losing the file permanently — there is no recovery, by design.
Protecting formulas while allowing data entry
The most common real use case: a calculator, budget template or form where people fill in the inputs and the formulas must survive contact with them.
Hiding the formulas as well as locking them is often worth doing. In the Format Cells Protection tab there’s a second checkbox, Hidden. Tick it on your formula cells and, once the sheet is protected, the formula bar shows nothing when those cells are selected. The result still displays; the workings don’t.
That isn’t security either — anyone determined can unprotect and look. It’s about keeping a clean interface and stopping people copying a formula they don’t understand into somewhere it doesn’t belong.
Different rules for different ranges
Review › Allow Edit Ranges is the feature almost nobody finds, and it’s the right answer for shared workbooks.
It lets you define named ranges that specific people can edit, each with its own password, on top of the general sheet protection. So the sales team can edit the forecast block, finance can edit the costs block, and neither can touch the other’s — all on one protected sheet.
Set the ranges up before protecting the sheet; the option greys out afterwards. On a domain network you can assign permissions by user account instead of passwords, which is considerably tidier.
Unprotecting, and the forgotten password
To make changes yourself, go to Review › Unprotect Sheet, enter the password if there is one, edit, then protect again. There’s no way to stay in an ‘author mode’ — you unprotect and reprotect each time, which is mildly annoying and by design.
If you’ve forgotten the password on your own file, recovery is possible precisely because the protection is weak. Save a copy, rename it from .xlsx to .zip, open it, find the sheet XML inside the xl/worksheets folder, and delete the <sheetProtection> tag. Rename back and the protection is gone.
That works because sheet protection was never meant to withstand attack — which is the same reason it shouldn’t be relied on for anything sensitive.
Locking cells in a shared or online workbook
Sheet protection behaves the same in Excel for the web and in co-authored files, with one practical difference: unprotecting is a shared action. If a colleague unprotects to make a change and forgets to reprotect, the sheet stays open for everyone.
On files stored in OneDrive or SharePoint, version history is the better safety net anyway. Right-click the file, choose Version history, and you can restore any earlier state — which recovers from an overwritten formula far more reliably than protection prevents one.
Protection and version history solve different halves of the problem. Protection stops the mistake; version history undoes it when someone unprotects and makes it anyway.
Checking your protection actually works
Always test before sending. Protect the sheet, then try to type into a formula cell — you should get ‘The cell or chart you are trying to change is on a protected sheet’.
Then try the things people actually do: paste a block of data over the input area, add a row, sort a column, delete a range. Each of those is a separate permission in the Protect Sheet dialog and it’s easy to have blocked something users legitimately need.
Tab through the sheet as well. With ‘Select locked cells’ unticked, Tab should hop cleanly from one input to the next in a sensible order. If it jumps somewhere odd, you have an unlocked cell you did not intend to leave open.
Protecting the workbook structure
Sheet protection covers cells. It doesn’t stop anyone deleting the entire worksheet, renaming it, or reordering tabs.
For that, use Review › Protect Workbook. It prevents adding, deleting, hiding, unhiding, renaming and moving sheets. On a multi-sheet template with a hidden calculations tab, this is what stops someone unhiding it and having a look.
The two are independent. A workbook can have protected structure and completely editable cells, or the reverse. Most finished templates want both.
- ✓Ctrl+A and untick Locked before you start
- ✓Unlock only the cells people should type into
- ✓Give input cells a visible fill colour
- ✓Tick ‘Insert rows’ if users need to add records
- ✓Use Encrypt with Password for anything genuinely confidential
- ✕Ticking Locked on formulas and expecting that to be enough
- ✕Treating a sheet-protection password as security
- ✕Setting up Allow Edit Ranges after protecting — it greys out
- ✕Forgetting an Encrypt with Password — that one is unrecoverable
- ✕Assuming sheet protection stops someone deleting the sheet
Frequently asked questions
How do I lock only certain cells in Excel?
Press Ctrl+A, then Ctrl+1 and untick Locked on the Protection tab to clear every cell. Select the cells you want protected and tick Locked on those. Then go to Review, Protect Sheet — nothing takes effect until you do.
Why is my whole sheet locked?
Because every cell is Locked by default and protecting the sheet activates that flag everywhere. You need to unlock your input cells first, then protect.
Does locking cells require a password?
No. Protect Sheet works with the password field left blank, which stops accidental edits while letting anyone unprotect deliberately. Add a password only if you want to make that step intentional.
Can someone remove Excel sheet protection?
Yes, easily. Sheet protection passwords are not encryption and free tools remove them in seconds. Use File, Info, Protect Workbook, Encrypt with Password if the data is genuinely confidential.
How do I hide formulas but show results?
Select the formula cells, press Ctrl+1, and on the Protection tab tick Hidden as well as Locked. Once you protect the sheet, the formula bar stays empty for those cells while results still display.
How do I let users sort or filter a protected sheet?
In the Protect Sheet dialog, tick Sort and Use AutoFilter. Both only work on unlocked ranges, so the data you want sortable must be unlocked first.
- →Clean the data before locking it down — how to remove duplicates in Excel
- →Lay out headings safely: how to merge cells in Excel
- →Reveal what someone hid — how to unhide columns in Excel
- →Build the lookups you are protecting: how to use VLOOKUP