How to stop people breaking formulas in a shared Google Sheet™
Every shared sheet has the same story: a totals column that worked in April shows wrong figures in June, because someone typed a number over a formula. The fix has two halves: find the cells already broken, then lock the formula areas so it cannot happen again.
Find the typed-over cells
- Click a cell you trust in a formula column and read its formula in the formula bar.
- Move down the column. Any cell holding a plain number where its neighbours hold formulas is broken.
- Check the cells around it too: typed-over values cluster, because whoever broke one usually broke several.
Lock the formula areas
- Select the formula columns or ranges.
- Choose Data → Protect sheets and ranges → Add a sheet or range.
- Set permissions to Only you (or a short list of keepers).
- Leave the input cells unprotected, so collaborators can still do their part.
Add a note to the protected ranges explaining what they are: "Totals: calculated, do not type here." People respect a locked range with a reason on it.
Check the whole sheet at once
Shared Sheet Guardrails™ checks a Google Sheets™ file for typed-over formulas, lost validation, broken filters and the rest, and shows how to fix each one by hand. Checking any sheet is free, and you can take guardrails off in one click.
Install from the Google Workspace Marketplace™
Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. App Garden™ is not affiliated with or endorsed by Google.