📎 This article includes 1 downloadable practice file ↓
You share a quote calculator and a week later the GST formula has been typed over with a number. Sheet protection prevents that: people type in the input cells, and everything else is locked. The order of steps matters, and Excel has several kinds of “protection” that do very different things.
In this article
How locking works
Every cell has a Locked setting, and it’s on by default. It does nothing until you protect the sheet. So the method is always:
- Unlock the cells people should type in.
- Protect the sheet.
Step 1: unlock the input cells
Select the inputs (hold Ctrl to pick separate ranges), press Ctrl+1, open the Protection tab and untick Locked. Give inputs a light fill colour so users know where to type.
Step 2: protect the sheet
Review › Protect Sheet. The password is optional. Then choose what users may still do:
| Option | Tick it when |
|---|---|
| Select locked cells | Untick it to let the Tab key jump only between input cells, which makes a form very clean |
| Format cells / columns / rows | Users need to widen columns |
| Insert rows | It’s a growing list (formulas in inserted rows still need care) |
| Sort, Use AutoFilter | Users filter a report; the range must be unlocked to sort |
| Edit objects | Leave unticked so charts and buttons can’t be moved |
Hide formulas too
Locked cells can still show their formula in the formula bar. To hide it, select the formula cells, Ctrl+1 › Protection › tick Hidden, then protect the sheet. The result shows; the formula doesn’t.
Different people, different ranges
Review › Allow Edit Ranges lets you give a range its own password: the sales team gets the quantity column, finance gets the price column, all on one protected sheet.
Protect the workbook structure
Review › Protect Workbook stops people adding, deleting, renaming, hiding or unhiding sheets. Use it to keep a hidden “Lists” or “Rates” sheet hidden. It does not lock cells; combine it with sheet protection.
Real security: encrypt with a password
Sheet and workbook protection are guards against accidents. Their passwords are weak and free tools remove them in seconds. To keep a file confidential (salaries, customer data), use File › Info › Protect Workbook › Encrypt with Password. Without that password the file cannot be opened at all.
Protect many sheets at once
Excel protects one sheet at a time. For a 12-month workbook, a short macro does it:
Sub ProtectAll()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="", UserInterfaceOnly:=True, AllowFiltering:=True
Next ws
End SubUserInterfaceOnly:=True lets your own macros change protected sheets while users still can’t, but it resets when the file is closed, so run it from Workbook_Open.
Where people go wrong
| Mistake | What happens |
|---|---|
| Protecting before unlocking inputs | Nobody can type anywhere |
| Trusting sheet protection for secrets | Anyone can remove it; use file encryption |
| Sort and filter ticked but the data is locked | Sort still fails; unlock the data range |
| Forgetting merged or new rows | New rows inherit the lock of the row above |
Practice
Download the practice workbook below: a small quote calculator with a Checklist sheet that reads each cell’s lock status and turns green as you unlock the inputs and keep the formulas locked. Then protect the sheet and try to type over a formula.
Next: Data validation.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Protect-sheet practice workbookA quote calculator with a live checklist that turns green as you unlock inputs, lock formulas and protect the sheet.⬇ XLSX · 7 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.