Sheets Setup
Protect Formulas While Still Editing Inputs
Lock formula cells and unlock input ranges so coworkers can edit Actuals or Qty without breaking SUM and VLOOKUP rows.
Shared budget and inventory sheets break when someone deletes a SUM row. The fix: lock every cell by default, then unlock only input cells before turning on sheet protection.
Example: monthly budget sheet — formulas in column B totals and C24 leftover; users type Actuals in column C only.
Excel — step by step
1. Unlock input cells only
- Ctrl+A to select the sheet
- Ctrl+1 → Protection tab → check Locked (default) → OK
- Select C4:C22 (Actual column — adjust to your sheet)
- Ctrl+1 → Protection → uncheck Locked → OK
2. Protect the sheet
Review → Protect Sheet
- Set a password optional (for casual teams, blank password still stops accidental edits)
- Uncheck Select locked cells if you want stricter UX (optional)
- Keep Select unlocked cells checked
Click OK.
3. Test
Try editing C10 (unlocked) — works. Try editing C7 (formula total) — blocked.
Shortcut after protection: Alt+R, P, P opens Protect Sheet (Excel ribbon).
Google Sheets
- Data → Protect sheets and ranges
- Sheet tab → select your tab → Set permissions → Restrict who can edit → Only you (or named editors)
- For input areas: Range tab → Add range C4:C22 → Set permissions → Show a warning or allow specific people
Sheets does not use the same global Locked flag as Excel. Protection is range-based: protect the whole sheet except inputs, or protect formula ranges explicitly.
Alternative: share as Viewer, duplicate for each editor (simplest for small teams).
Allow inserting rows (optional)
Excel Protect Sheet dialog: check Insert rows if users add new budget lines but keep formula columns locked. New rows inherit lock state from the row above — unlock new input cells in that row.
Inventory template pattern
- Unlock: C (On Hand), D (Reorder At)
- Lock: E (Status formula), F (Order Qty)
If someone overtypes Status with text, conditional formatting may stop working.
Wrong vs Right
| Wrong | Right |
|---|---|
| Protect sheet before unlocking inputs — nobody can edit anything useful | Unlock inputs first, then protect |
| Lock only B7 one cell — user deletes row 14 formulas | Lock all formula columns; unlock contiguous input range |
| Password on sheet, password in email body | Use password manager or warning-only in Sheets |
Related
After protection, import new rows via CSV import into an Inputs tab, then reference with formulas on the locked summary tab.