Home › Excel basics › Lock cells & protect sheet
How to Lock Cells and Protect a Sheet in Excel
Locking cells in Excel takes two steps: decide which cells stay editable, then turn on Review › Protect Sheet. Every cell is "locked" by default, but that only takes effect once the sheet is protected. Looking for the $ signs that lock a cell in a formula? Jump to lock a cell in a formula.
Excel CSE · In this guide
Lock all cells: protect the sheet
- Go to Review › Protect Sheet.
- Enter a password if you want one (optional).
- Choose what others may still do, such as selecting cells, sorting or using filters.
- Click OK and confirm the password.
Anyone who tries to edit a cell now gets a message that the cell is protected. Turn it off with Review › Unprotect Sheet.
Leave some cells editable
Usually you want people to fill in certain cells (inputs) but not touch the rest (formulas, headers).
- Select the cells that must stay editable. Hold Ctrl to select several areas.
- Press Ctrl+1, go to the Protection tab and untick Locked.
- Protect the sheet via Review › Protect Sheet.
Users can now type in those cells only. Give them a fill colour so it's clear where input goes. Tip: on a protected sheet, Tab jumps from one unlocked cell to the next.
Lock only the formulas
- Select the whole sheet (Ctrl+A), press Ctrl+1 › Protection and untick Locked.
- Press F5 › Special, choose Formulas and click OK. Excel selects every cell with a formula.
- Press Ctrl+1 › Protection and tick Locked (and Hidden to hide the formula in the formula bar).
- Protect the sheet.
Now all values can be changed, but the formulas can't be overwritten or deleted.
Protect the workbook structure
Sheet protection doesn't stop someone from deleting, renaming or adding sheets. For that, use Review › Protect Workbook. To stop people from opening the file at all, use File › Info › Protect Workbook › Encrypt with Password.
| Protection | Stops |
|---|---|
| Protect Sheet | Changing cells, formatting, rows and columns on one sheet |
| Protect Workbook | Adding, deleting, moving, hiding or renaming sheets |
| Encrypt with Password | Opening the file without the password |
Not real security. Sheet and workbook protection prevent accidents, not determined people: they can be removed with free tools. Only the file password (encryption) is strong. Don't lose it: Microsoft can't recover it.
Allow editing for specific ranges
With Review › Allow Edit Ranges you can give a range its own password, so different people can edit different parts of the same protected sheet.
Lock a cell in a formula ($)
This is something else entirely: making a reference stay the same when you copy a formula. Put $ signs in front of the column and row, or press F4 after typing the reference:
=B2 * $E$1Copy this down and B2 changes to B3, B4…, but $E$1 stays fixed. Full explanation in absolute references.
Frequently asked questions
Why can I still edit locked cells?
Locking only works when the sheet is protected. Go to Review › Protect Sheet.
How do I unlock a protected sheet without the password?
There's no official way. Ask the person who protected it, or check whether an older, unprotected version is in File › Info › Version History.
Can people still use filters on a protected sheet?
Only if you tick "Use AutoFilter" (and "Sort") in the Protect Sheet dialog, and the filter was already turned on before protecting.
How do I lock the top row so it stays visible?
That's freezing, not locking: View › Freeze Panes › Freeze Top Row. See freeze panes.