Help for owners
Somebody edits my Google Sheet and the formulas break every time
Short answer
Formulas break when someone inserts, deletes or sorts cells, overwrites a formula with a typed number, or renames a tab the formula points to. Use version history to see who changed what and restore a good copy, then protect the cells that hold formulas.
Does this sound like your situation?
- Cells show #REF! or other error messages.
- A total no longer matches the numbers above it.
- A formula has been replaced with a plain number.
- It breaks right after a colleague sorts or adds a column.
- I fix it and a week later it is broken again.
Why does this happen?
Deleting or moving cells that formulas point to
A formula refers to cells by position. If a row or column it depends on is deleted, the reference is lost, which shows as an error.
Typing over a formula
When someone types a number into a cell that held a formula, the cell stops updating. Everything that depends on it stays wrong without any warning.
Sorting, renamed tabs and copied files
Sorting only some columns scrambles the link between a row's inputs and its results. A formula that points to another tab or file breaks when that tab is renamed, moved or the file is replaced by a copy.
Nobody can see who changed what
Google keeps a version history and cell edit history, but only people with edit access can use it, and some changes do not show, including added or deleted rows and columns, formatting and changes made by formulas.
Confirmed by support.google.com
Protection is not set up
Google lets owners and editors protect a sheet or range, so only chosen people can edit it, or so others get a warning first. Without it everyone with edit access can overwrite anything.
Confirmed by support.google.com
What can I do about it today?
- Open version historyClick Last edit, pick an earlier version and look at who changed what. Viewing needs edit permission.
- Make a copy instead of restoring blindlyGoogle notes that restoring a version clears the cell-by-cell edit history for earlier changes. A copy of the good version keeps that history.
- Name the good versionSheets lets you name up to 15 versions per spreadsheet. Name the last one you trust.
- Separate inputs from formulasAgree one tab or one area where people type, and keep formulas in another area.
- Protect the formula cellsUse Data, then Protect sheets and ranges, and allow editing only for yourself or one named person. Choosing the warning option is the gentlest start.
When should I bring someone in?
- The sheet drives invoices, payroll or stock and wrong numbers have already been used.
- You cannot tell which of several copies is correct.
- The same breakage returns even with protection.
How do I handle it?
- Compare good and broken versionsI use the history to find the first bad edit and the cells it affected.
- Rebuild formulas so they survive editsRanges are made robust, lookups use stable keys instead of positions, and the logic is documented in a short note.
- Add checksTotals are cross-checked, and a visible status cell turns red when inputs or results look wrong.
- Protect and separateInput, calculation and report areas are split and protected so staff can only edit what they should.
- Judge the future of the sheetIf the logic is complex and many people rely on it, I propose moving it to a small application.
The price depends on what I find, so I quote in writing after a short discovery call. Tell me what is happening and I reply within 24 hours.
What do owners ask about this?
Can I see who changed one cell?
Right-click the cell and choose Show edit history. Google says some changes do not appear, such as added or deleted rows and columns.
Does protecting a sheet make it secure?
Google states protection is not a security measure: people can still print, copy and export. It prevents accidents, not data theft.
Should I use Excel instead?
The same breakages happen in any spreadsheet. The cure is structure and protection, or moving to software.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Found a mistake? Tell me.