To lock formulas while allowing data entry in Excel, remember that every cell is Locked by default and that the setting does nothing until the sheet is protected. So the order is: select only the input cells, clear the Locked checkbox in Format Cells, then protect the sheet. Everything you did not clear becomes read-only, and the input cells stay editable. The same protection locks formatting too, including in the input cells people type in.
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.
Setting up cell locking and sheet protection
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.
Locking Formatting While Allowing Data Entry
Protection locks formatting as well as formulas. While Format cells, Format columns and Format rows stay unchecked in the Protect Sheet dialog, which is how the dialog opens, nobody can change fonts, fills, borders, number formats, column widths or row heights, including in the input cells they are allowed to type in.
Two things still work. Conditional formatting keeps coloring cells as values change, although its rules cannot be added, edited or deleted until the sheet is unprotected. And pasting into an input cell brings in the value only: in Microsoft 365 the cell keeps its own formatting, so a paste from another workbook does not undo your layout.
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
Common questions
What if I forget the sheet protection password?
Excel offers no recovery. Always keep an unprotected master copy of any workbook you protect, because there is no supported way back in.
Is sheet protection real security?
No. It prevents accidents, not determined access, and the password is easily removed by anyone who wants to. Never rely on it to hide confidential data.
Can users still copy the protected cells?
Yes, by default. Clear Select Locked Cells in the Protect Sheet dialog to prevent selection, though this also makes the sheet harder to navigate.
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.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.