Why your spreadsheet keeps breaking
The four failure patterns behind every spreadsheet nobody trusts — positional references, missing keys, silent errors and no audit trail — and the fix for each.
Somewhere in your business is a spreadsheet that three people edit, one person understands, and nobody fully trusts. It breaks every few months and someone spends a day fixing it.
It is not breaking because spreadsheets are bad. It is breaking for four specific reasons, and each has a fix.
Failure one: everything is addressed by position
=Sheet2!D14 means “whatever happens to be in column D, row 14.” It does not mean “the cost of this product.” Those are the same thing right up until someone inserts a column, and then the formula is confidently wrong rather than visibly broken.
The fix: look things up by what they are, not where they sit. Find columns by their header text and rows by a stable identifier. Then the sheet can be reordered, extended and restyled freely, which people will do regardless of what you ask of them.
This single change eliminates most spreadsheet breakages, and it does it by making the file resilient to normal human behavior rather than by asking humans to behave differently.
Failure two: nothing has a stable key
The costing sheet identifies products by barcode. The sales sheet uses SKU. The supplier list uses a name with inconsistent capitalisation. None of them is complete.
So reconciliation happens by eye, which is slow, and by name matching, which is wrong — because “Blue Widget 500ml” and “Blue Widget 500 ml” are different strings and the same product.
The fix: pick a resolution order and apply it every time, from scratch. Try barcode, then SKU, then whatever else you have. Never cache the result, so a product that gains a barcode next month simply matches on the better key without anyone reconfiguring anything.
Fuzzy name matching feels like the solution here. It is not — it is how you end up merging two genuinely different products and never finding out.
Failure three: errors are silent
A formula returns a blank instead of a number. The total is still a number, so nothing looks wrong. The figure is quietly understated and somebody prices a product from it.
Silent wrong answers are the expensive failure mode. Loud failures cost an hour; silent ones cost whatever decision was made on top of them.
The fix: make the sheet assert what it expects. Row counts that should match, totals that should tie, fields that should never be empty. Put those checks somewhere visible and make failure obvious — red, at the top, unmissable. A file that says “something is wrong” is worth more than one that looks fine and is not.
Failure four: no audit trail
Someone changed a number three weeks ago. It was probably right. Nobody can say what it was before, or why it changed, or who did it.
Google Sheets version history helps a little, but it answers “what did this file look like on the 14th?” — not “why is this specific cell different?”
The fix: whenever something writes into the sheet automatically, log what it wrote, what was there before, and when. It sounds like bureaucracy until the first time someone asks about a number and you can answer in ten seconds instead of an afternoon.
The pattern underneath all four
Every one of these is the same mistake: the spreadsheet knows where things are but not what they mean.
Once a file addresses data by identity rather than position, checks its own assumptions, and records what changed, it stops being fragile. It becomes something you can hand to someone else without a twenty-minute briefing on which columns not to touch.
That is usually the real goal. Not a fancier tool — just a file that does not need you personally to keep working.
If your monthly reconciliation is eating days, that is a solvable problem — here is a write-up of exactly this, solved for a real client. Or book a free call.