You build a workbook with formulas, formatting, and structure — then share it with users who accidentally delete formulas, overwrite calculated cells, or rearrange columns. Sheet protection lets you lock everything down while leaving specific input cells editable. Here’s how to set it up properly.
The Concept
Excel’s protection works in two layers: first you mark which cells should be unlocked (editable), then you protect the sheet. Everything not explicitly unlocked becomes read-only.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Quantity | Unit Price | Total |
| 2 | Widget A | Enter qty → | $25.00 | =B2*C2 |
| 3 | Widget B | Enter qty → | $18.50 | =B3*C3 |
Gray cells are locked (formulas, labels, prices). Blue-bordered cells are unlocked (user enters quantities). The Total formula is protected — users can’t accidentally delete it.
Step-by-Step Setup
1Select all cells (Ctrl+A), right-click → Format Cells → Protection tab → make sure Locked is checked. By default, all cells are locked — this is the starting point.
2Select only the input cells you want users to edit (in our example, B2:B3). Right-click → Format Cells → Protection tab → uncheck Locked.
3Go to Review → Protect Sheet. Optionally set a password. Under “Allow all users of this sheet to,” check Select unlocked cells (and uncheck “Select locked cells” if you want users to only be able to click on input cells).
4Click OK. The sheet is now protected.
Visual cues for users
Give unlocked cells a different background color (light yellow or light blue) so users can immediately see where they’re supposed to type. This reduces confusion and support requests.
Allowing Specific Actions
The Protect Sheet dialog has checkboxes for what users can do. Common settings:
For a data entry form: Check “Select unlocked cells” only. Users can tab between input fields but can’t click on or see formulas.
For a shared report: Check “Select locked cells”, “Select unlocked cells”, “Sort”, and “AutoFilter”. Users can view everything and filter data but can’t edit formulas.
For a template: Check “Insert rows” and “Delete rows” if users need to add data rows while keeping the header and formula structure intact.
Protecting Structure (Preventing Sheet Changes)
To prevent users from adding, deleting, renaming, or reordering sheets:
Go to Review → Protect Workbook → check Structure → set a password.
Protection is not security
Excel sheet protection is designed to prevent accidental changes, not to secure sensitive data. The password can be removed with freely available tools. If you need real data security, use file-level encryption (File → Info → Protect Workbook → Encrypt with Password) or restrict access at the network level.
Hiding Formulas
Even with protection, users can see formulas in the formula bar when they click a locked cell. To hide them:
1Select the formula cells
2Right-click → Format Cells → Protection tab → check Hidden
3This only takes effect when the sheet is protected
When Formulas Aren’t Enough
Basic sheet protection works for simple templates. But professional-grade workbooks often need more control:
- Role-based access — managers can edit certain cells that regular users can’t
- Dynamic locking — cells that lock automatically after data is entered (preventing changes to submitted data)
- Audit logging — tracking who changed what and when
- Input validation with custom messages — rejecting invalid entries with helpful error messages
- Auto-protection — the sheet re-protects itself after VBA macros make programmatic changes
A custom VBA protection system implements role-based permissions, dynamic locking, change logging, and intelligent validation — keeping your workbook bulletproof while remaining user-friendly.
“Easy to work with; great results; would recommend.”
Excel Protection Client · Upwork
Need a Bulletproof Excel Template?
Tell us about your workbook and we’ll build one that users can’t break.
Start a Project