The short answer: it's the structure, not careless people
When a shared spreadsheet keeps breaking, the instinct is to blame whoever touched it last. That's the wrong diagnosis. The real cause is structural: everyone can edit every cell, and you keep adding more people to that setup. Accidental overwrites during simultaneous editing, broken references after a row insert, and permission slip-ups aren't behaviour problems you can train away — they're baked into how an open grid works.
In practice, the fix comes in two layers. First, immediate damage control using built-in features: protect formula ranges, add data validation, tighten edit access, and split "view" from "input." Then, if the sheet has to survive a growing team, the durable fix is to move data entry behind a form plus a database so people stop editing the master grid directly. This article walks through both, and is honest about where each one stops working.
Why shared spreadsheets break: four structural causes
Across real-world cleanup work, broken shared sheets almost always trace back to the same four causes. None of them are solved by "being more careful" — they recur until the structure changes.
1. Overwrites and mix-ups during simultaneous editing
Google Sheets lets many people edit the same sheet at once, but it does not lock individual cells. If two people type into the same cell at nearly the same moment, the edit that lands last wins — the other person's value is silently gone. A close cousin: someone scrolls, misjudges the row, and overwrites a record they never meant to touch.
There's also a hard ceiling. Google's official limit is up to 100 people viewing, editing, or commenting on a file at the same time; beyond that, only the owner and some editors can keep editing. If large-group, concurrent entry is the whole point of your sheet, that limit is a structural cap you'll eventually hit.
2. Inserting or deleting rows breaks formulas and references
The most common "my formulas broke" complaint comes from row inserts and deletes. A normal A1-style formula such as =SUM(B2:B10) uses relative references, so when a row is inserted just outside the referenced range, the range doesn't follow and the new row drops out of the total. Delete a row inside the range and you get a #REF! error that instantly cascades to every cell that depended on it.
In a multi-user sheet this is brutal. One person builds the summary, another "just adds a row," and the first person's totals quietly drift out of sync. Nobody did anything obviously wrong, yet the numbers stop adding up. That mismatch is almost always a reference problem, not a typo.
3. Permission accidents — when anyone can edit, everything is fragile
Collaborators get one of three roles: Viewer, Commenter, or Editor. The classic mistake is choosing convenience and making "anyone with the link" an Editor. That means anyone the link reaches — including people it was forwarded to — can rewrite any cell, including the ones holding your formulas. A sheet built that way is engineered to break.
4. No separation between "input" and "calculation"
Most sheets pile data entry and reporting onto a single tab. That's fine for two or three people. As the team grows, the odds rise that an inputter will overwrite a summary cell or a header row. Even if each person's error rate stays constant, more people means more total accidents — which is exactly why the same sheet "works fine" at three users and falls apart at fifteen.
Damage control: 5 steps to make a sheet harder to break
Before rebuilding anything, exhaust what the built-in features can do. Applied in order, these steps cut accidents dramatically.
Step 1: Lock formulas and headers with "Protect sheets and ranges"
Go to Data → Protect sheets and ranges and protect the ranges that hold formulas and headers. Restrict editing to "Only you" or "Custom," and inputters can no longer touch your summary logic. A practical pattern: protect the whole sheet, then use "Except certain cells" to open up only the input fields.
An honest caveat: protection also offers a "Show a warning" option, but that only displays a confirmation message — it does not block the edit. If you actually want to protect something, choose the permission restriction (Only you / Custom), not the warning. Remember too that protection is not a security feature: anyone with edit access can remove the protection itself.
Step 2: Reject bad values with data validation
For input fields, set rules under Data → Data validation. Dropdown lists kill inconsistent entries (Tokyo / TOKYO / tokyo all collapse into one), and date or number checks catch malformed input. Set the rule to "Reject input" so values that would break a formula never make it into the cell in the first place.
Step 3: Minimize edit access
Drop "anyone with the link can edit" as a default. Invite only the people who genuinely need to edit, individually as Editors, and make everyone else a Viewer. Simply not granting edit rights to people who only need to read shrinks the pool of possible mistakes immediately.
Step 4: Physically separate "view" from "input"
Keep the reporting/summary sheet (or a separate file) apart from a dedicated input sheet. Make the input sheet a minimal, dropdown-driven layout, and have the summary sheet pull from it one-directionally with functions like IMPORTRANGE or QUERY. When the place people type is separate from the place the math happens, edits to the input side are far less likely to break the logic. Separating "where you touch" from "where it calculates" is the heart of damage control.
Step 5: Make it traceable with version history and alerts
Zero accidents isn't realistic, so build a way to roll back. File → Version history → See version history lets you review and restore earlier versions, with per-editor colour coding so you can see who changed what. Pair protection warnings on critical ranges with a simple operating rule — adding or deleting columns must go through an admin — and you stop most reference-drift accidents before they happen.
How it breaks | Damage control | Limit that remains |
|---|---|---|
Overwrites / simultaneous-edit mix-ups | Separate input sheet, minimize editors | Same-cell conflicts can't be fully prevented |
Row inserts breaking formulas/references | Protect summary cells, limit column ops to admins | Relies on rules — becomes one person's job |
Permission accidents (anyone can edit) | Invite editors individually only | Editors can still remove protection |
Inconsistent or unexpected values | Data validation (reject input) | Cell-level control is the ceiling |
Where quick fixes stop: someone still edits the grid by hand
The steps above genuinely help, but they hit a real wall. A spreadsheet's value is that it's a freely editable grid — and that's also its weakness. Protection can be removed by any editor, data validation is mostly cell-level, and nothing fully stops someone from breaking the row-and-column structure itself.
The bigger problem is that it all becomes one person's job. Rules like "don't touch this column" and "ask an admin before adding rows" erode as the team grows and turns over. The more you patch with process, the more likely it is that a single person ends up carrying the maintenance — and the whole thing breaks the week they're on holiday.
The durable fix: a form plus a database that can't break
To make a sheet survive a growing team, change the model. Move from "everyone edits one shared grid" to "everyone enters data through a fixed form, and the data lands in a database." Structurally, this stops most breakage at the source:
- Inputters never touch the master grid. Because entry goes through a form, there's no way to directly break a formula or a header row.
- Reference drift can't happen. Data is appended as records (rows), so the "row insert shifts my formula" accident is impossible by design.
- Permissions become field-level. You control who can enter or see each field far more precisely than sheet-level cell protection allows.
- It handles concurrency. Each person submits their own entry, so there's no fight over the same cell.
The honest trade-offs: a form-plus-database setup has upfront design and build cost, and you lose the free-form, edit-anything-on-the-spot flexibility of a raw sheet. For small, short-lived, or still-changing use cases, the spreadsheet quick fixes are often enough — don't over-engineer. The payoff appears for work that can't afford to break, keeps adding people, and runs for the long haul.
Turn the sheet into an app without throwing away your workflow
At Mihata, we rebuild spreadsheets that break under multi-user use into a form-plus-database app — keeping the columns and workflow you already have rather than reinventing the process from scratch. A sensible first step isn't a rebuild at all: it's deciding which parts are truly broken, and whether you even need an app yet or the quick fixes above are enough.
Frequently asked questions
Does protecting a sheet make it unbreakable?
No. Protection is effective at preventing accidental edits by inputters, but anyone who holds edit access can remove the protection itself. And the "show a warning" option doesn't block edits — it only asks for confirmation. It's not a complete safeguard.
Is there a limit on how many people can edit at once?
Yes. Google's official limit is up to 100 people viewing, editing, or commenting on a single file simultaneously; past that, only the owner and some editors can keep editing. If large-group use is the plan, that's a good signal to consider moving to an app early.
Why do the numbers stop matching after I just add a row?
Because relative (A1-style) formulas don't automatically widen their range when a row is inserted outside it. Protect the summary cells and restrict row/column changes to an admin — or move to a record-appending database, which removes the problem at the root.