Excel Tutorials
Protecting cells in Excel works backwards from how most people expect: every cell is locked from the start, and you unlock the ones you want people to use. This lesson covers that two-step process, hiding formulas, workbook structure protection, per-range permissions and where protection genuinely stops being security, using the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
Cell protection stops a worksheet being edited in ways you did not intend: formulas overwritten, headers renamed, a template quietly broken by someone typing over a calculation. It is the difference between handing out a workbook and handing out a form.
The part that confuses almost everyone is the two-step design. Locked is an attribute on each cell, and by default it is already switched on for every cell in a new sheet. On its own it does absolutely nothing. It only takes effect when you turn on Protect Sheet, which is the master switch that enforces the locked attribute across the whole worksheet.
That means protecting a sheet is really an unlocking job. You select the cells people are supposed to type into, unlock those, and then protect the sheet, so everything else becomes read-only in one action. Get that order round the wrong way and you end up with a sheet nobody can enter anything into at all.
Step-by-step, matching the video above.
In a brand new worksheet every single cell has the Locked attribute switched on. It does nothing at all until you protect the sheet, at which point all of those locked cells become read-only at once.
Because everything starts locked, protection is a subtraction job. Highlight the input cells, the ones people are meant to type into, and unlock those. Hold Ctrl to pick several separate ranges at the same time.
Press Ctrl+1 to open Format Cells, go to the Protection tab and untick Locked, then click OK. Nothing appears to change yet, which is expected.
On the same Protection tab, Hidden stops the formula appearing in the formula bar once the sheet is protected. The result still calculates and displays normally, the underlying formula is just not visible.
Go to Review > Protect Sheet. This is the switch that makes everything above take effect: locked cells become read-only and unlocked cells stay editable.
The checklist in the Protect Sheet dialog controls the exceptions. Sorting, filtering, formatting cells, inserting rows and using PivotTable reports can each be permitted even while the cells themselves stay locked.
A password stops people simply clicking Unprotect Sheet. Leaving it blank still prevents accidental edits while letting anyone lift protection on purpose, which is often the right call for an internal team template.
Click a locked cell and try to type, then click an input cell and confirm it accepts entry. Tab through the sheet: with protection on, Tab jumps between unlocked cells only, which is a genuinely nice way to guide data entry.
Protecting a sheet guards the cells. Protecting the workbook guards the sheets themselves.
This stops sheets being added, deleted, renamed, moved, hidden or unhidden. It is a separate setting from sheet protection and neither one implies the other.
If you have a calculation or lookup sheet hidden out of the way, workbook protection is what stops someone right-clicking a tab and choosing Unhide.
Workbook protection has no effect on what people can type. You still need Protect Sheet on each individual worksheet to control cell editing.
| Cells | Locked? | Result After Protecting the Sheet |
|---|---|---|
| Headers in row 1 | Locked | Cannot be renamed or deleted |
| Budget inputs C4:C20 | Unlocked | Fully editable, Tab moves between them |
| Total formula C21 | Locked + Hidden | Calculates, but formula bar shows nothing |
| Notes column E | Unlocked | Free text entry allowed |
Four cells, four different outcomes, all from one Protect Sheet action. The pattern to copy is that the formulas and labels stay locked so nobody can overwrite the logic, while the input column is unlocked so the template still does its job. Adding Hidden to the total keeps the calculation working while stopping people from picking apart or copying the formula.
Allow Edit Ranges gives different areas of one protected sheet their own separate passwords.
Review > Allow Edit Ranges is greyed out once protection is on. Define your ranges first, then protect the sheet.
Click New, select the range, name it something meaningful like Catering Budget and set a password. Anyone with that password can edit that range while the rest of the sheet stays locked to them.
One budget sheet where each department can only edit their own rows is the classic case, without splitting the file into separate workbooks that then have to be merged back together.
Worth being clear about, because it is routinely mistaken for security.
Sheet protection prevents accidental edits. It is not designed to withstand someone determined to remove it, and the password is not a strong cryptographic protection.
File > Info > Protect Workbook > Encrypt with Password requires a password to open the file at all. That is genuine encryption, and losing the password means losing the file permanently.
Unless you untick Select locked cells, people can still select, copy and paste your protected data somewhere else. Protection controls editing, not distribution.
There is no built-in reset and Microsoft support cannot retrieve it. Record protection passwords somewhere separate from the workbook itself before you set them.
Every cell is locked by default, so protecting straight away makes the entire sheet read-only. Unlock the cells people need to type into before you click Protect Sheet.
Ticking Locked in Format Cells has no visible effect until the sheet is protected. Both halves are required, and the Review tab is the half people forget.
There is no recovery mechanism. Excel cannot reset it and neither can Microsoft support. Store protection passwords somewhere other than the workbook they protect.
It is designed to prevent accidents, not to withstand someone determined to get around it. For anything genuinely confidential, use File > Info > Encrypt with Password instead.
By default a protected sheet blocks both, which makes an otherwise fine report frustrating to use. Tick those permissions in the Protect Sheet dialog if people still need them.
The option is greyed out once protection is on. Define your edit ranges first, then protect the sheet.
Locked cells can still be selected and copied unless you untick Select locked cells. Protection controls editing, not whether data leaves the file.
Password-protecting a tab is what you do when the file has no idea who is opening it.
Free 14-day trial. No credit card required.
Every cell is locked by default, so protecting the sheet makes all of them read-only. Unprotect the sheet, select the cells you want people to type into, untick Locked in Format Cells, then protect the sheet again.
Select all cells and untick Locked, then select just the cells you want protected and tick Locked back on. Finally use Review > Protect Sheet to enforce it.
Ctrl+1 opens the Format Cells dialog, where the Protection tab holds the Locked and Hidden checkboxes.
Tick Hidden on the Protection tab of Format Cells for those cells, then protect the sheet. The formula stops showing in the formula bar while the result still displays and calculates.
No. Leaving the password blank still prevents accidental edits and lets anyone lift protection deliberately, which is often ideal for internal team templates.
No. Excel has no reset function for it and Microsoft support cannot retrieve it, so keep a record of protection passwords somewhere separate from the file.
Protect Sheet controls whether cells on a worksheet can be edited. Protect Workbook controls the structure, stopping sheets being added, deleted, renamed, moved or unhidden.
No. Cell protection prevents editing but the file still opens freely. File > Info > Encrypt with Password requires a password to open the workbook at all and is genuine encryption.
Yes, using Review > Allow Edit Ranges. Each range gets its own title and password, and you must set them up before protecting the sheet.
Only if you allow it. The Protect Sheet dialog has checkboxes for sorting, filtering, formatting and inserting rows, all off by default.
No, not unless you untick the option allowing users to select locked cells. Protection restricts editing, not selecting and copying.
Every business starts with a spreadsheet. Updoot is where you scale past it, records link themselves automatically.
Start Your Free Trial →