Google Sheets web app (rolling service) · Sheet and cell protection · Beginner

Protect formula cells and leave Google Sheets inputs editable

Build a shared inventory sheet with editable quantities, restricted formulas, collaborator checks, and clear limits on what protection secures.

Documentation-reviewed draft; not tested in Google Sheets. · Editorial standards

Protect the formula range with restricted editing permissions, while leaving the quantity range outside that protection. A warning alone will not stop a collaborator overwriting a formula.

Requirements and limits

Use Google Sheets in a desktop browser and a native Google Sheets file, with owner or editor access. An editor cannot remove the owner’s rights. If the protection command is missing in an Office file, convert a practice copy to Google Sheets first. This feature is not password protection or encryption: copied or exported data may remain accessible. Google also does not offer a protected-cell mode that locks input formatting while permitting value changes. Google protection help

1. Build a small inventory example

On a tab named Inventory, enter this layout in A1:C3. Quantities are inputs; C2 and C3 are formulas.

Item	Quantity input	Calculated total
Notebook	2	`=B2*4.50`
Pen	3	`=B3*1.25`

The expected totals are 9.00 and 3.75, or 12.75 together. These are synthetic practice prices. Keep the price constants inside the formulas for this tiny exercise; a production tracker usually needs a separately maintained price list.

In B5 enter Total value, and in C5 enter =SUM(C2:C3). The total is a formula too, so include it in the protection plan. A common failure is locking line-item calculations while leaving the final total editable.

2. Restrict the formulas

Select C2:C3. Choose Data → Protect sheets and ranges, then Add a sheet or range.
Choose Range, confirm Inventory!C2:C3, and label it Calculated line values.
Select Set permissions, then Restrict who can edit this range. Choose Only you or a specific trusted formula-maintenance group through Custom. Save.
Repeat for Inventory!C5, labelled Inventory total.

These steps follow Google’s range-protection workflow. Do not select the warning option when your goal is to block edits.

3. Check as the collaborator who enters stock

Your own account is not a sufficient permission test: you deliberately retained formula-editing access. Ask an existing authorized editor who is outside the formula-maintenance list to test a practice copy.

Change B2 from 2 to 5. Expected C2: 22.50; expected C5: 26.25.
Try replacing C2 with 999. That attempt should be blocked for this editor. If it succeeds, inspect the restriction and the account used for the test.
Try replacing C5. Protecting C2:C3 does not protect C5 automatically.
Restore B2 to 2. Confirm the total returns to 12.75.

Changing an input should still update a protected formula’s result. The protection is aimed at who may edit the cell, not at freezing its calculation.

4. Protect the rest of the layout when needed

For this compact example, a cleaner alternative is to protect the entire Inventory sheet and use Except certain cells for B2:B3. Set restricted permissions on that sheet. This covers the headings, product labels, formulas and total in one rule. It does not grant file-editing access to viewers. Sheet protection options

Treat the two approaches as alternatives: inspect existing overlapping protections before layering rules. Plan the input area before protecting the sheet. If staff will add new products, decide who maintains the template and updates the input exception and formulas. For example, inserting a third product requires checking the total’s coverage as well as the editable range.

Troubleshooting and recovery

An input cannot be edited: Check whether B2 is inside a second protected range or a whole-sheet protection without the intended exception. Also check the collaborator’s file role; a viewer does not become an editor because a range is unprotected.

The owner can change a formula: That is expected. Repeat the test using the intended collaborator role.

The numbers look wrong: Inspect C2 and C3 before changing permissions. They should still contain the two formulas above. Protection cannot repair an earlier overwrite. Re-enter the known practice formulas, then repeat the input test.

The team needs to copy or export: Do not represent protection as a way to prevent disclosure. Share the file only with people authorized to see its contents, and review sharing separately.

A formula has been added below the protected block: Include it in your review. A narrowly selected formula range is not a promise that all future formulas will be protected.

SUM explains the total; FORMULATEXT helps inspect stored formula text.

Testing disclosure: This tutorial was checked against Google’s documentation on October 16, 2026 and the example arithmetic was independently calculated. It has not been tested in Google Sheets; collaborator permission checks and screenshots remain unverified.