Spreadsheets break, formulas get deleted, and which version is this?
· 6 min read · The spreadsheet that became the system
An operations and admin person at a small business, writing in a forum thread about businesses held together with tape, counted three hours in a single day spent on manual data entry and on working out which spreadsheet was the current one. In the same post they described a date being missed after a reminder macro quietly stopped working.
Three hours is the part that gets quoted. The missed date is the part that costs money. Both come from the same place: a file that was built to help somebody with a task and has since become the place the business keeps its truth.
Nobody decides to run a business on a spreadsheet. It happens by addition. A tab is added because a system will not hold something. A formula is added because a rule changed. A second copy is made because two people needed it on the same afternoon. Every one of those decisions was correct on the day, and the result is a system of record that nobody designed, nobody owns and nobody can safely change. The back office automation work we do almost always starts here, because this file is where the business actually runs.
The four ways it fails
They break. A formula range stops at row 500 because it was written when there were 400 rows. A lookup silently returns the wrong row after a column is inserted. A macro that sent reminders stops sending them, and nothing announces it, so the failure is discovered by a customer.
They do not save. Two people in the same file, a network drive with a habit of dropping, a laptop that restarted overnight. The work is gone and, worse, nobody is certain how much of it was gone before anyone looked.
Formulas get deleted. Someone pastes values over a calculated column to fix one number, and the column stops calculating for everyone from then on. The file still opens. The numbers still look like numbers.
Nobody knows which version is the one. A file, a copy of the file, a copy with initials appended, and a copy in an email attachment that somebody has been editing for a fortnight. An owner in another thread described more than fifty documents holding sales data built up over years, representing hundreds of hours of entry and analysis. The value is real. The risk is that none of it is anywhere else.
Naming the four matters because they need different fixes, and because a business that has all four is not a business with a messy file. It is a business whose system of record has no backup, no audit trail, no access control and no owner.
The split that decides everything
The useful question is not how to replace the spreadsheets. It is what each one is doing, and there are only two answers that matter.
Some spreadsheets compute. They take inputs and apply the business's own logic to produce an answer: a price, a margin, a roster cost, a commission, an allocation across sites. The value in them is the logic, which usually exists nowhere else in the business and was worked out over years by someone who may no longer be there.
Some spreadsheets join. They take data out of two or three systems and put it side by side so a person can see one picture. The value in them is not logic at all. It is the joining, which is being done by hand every week or month because no system does it.
Almost every file in a business is one or the other, and a handful are both, which usually means they should be two files.
What happens to the ones that compute
The logic comes out of the formula bar and goes into a table your team can edit. Not into code: into rows, with a column for the condition and a column for the result, so the person who knows the rule can change the rule without asking anyone and without opening anything that can break.
This is the part that surprises people. The instinct is that turning a spreadsheet into software means the rules get locked away from the business. The opposite is the point. In a spreadsheet the rules are locked away already, inside formulas nobody dares touch, in a file where changing one thing can break another silently. Putting them in a table makes them visible, changeable and testable for the operator who owns them.
What happens to the ones that join
Those become a connection, and they stop being a job somebody does. Two or three systems are read on a schedule, matched on whichever identifier they already share, and written into one view with the time it last refreshed printed on its face.
The condition we hold ourselves to is that no second system of record gets created. The systems that hold the data keep holding it. The connection reads, it does not become the place the answer lives, because a business that has been burned by one undesigned system of record does not need a second.
The third group
A share of every inventory is neither. It is a report someone asked for once, a file kept because deleting it felt risky, a copy of a copy. Those get deleted, and the relief when they do is out of all proportion to the hours saved, because every one of them was a thing that could be picked up by mistake.
What the inventory looks like
One page, one row per file, and four columns that turn a vague mess into a decision anybody can argue with.
| File | What it does | Hours a month | What it becomes |
|---|---|---|---|
| Supplier invoice entry | Joins supplier PDFs to purchase orders | 16 | A connection, with exceptions raised to a person |
| Job pricing | Computes labour, materials and margin | 6 | Rules in a table the estimator edits |
| Weekly cash view | Joins the ledger and two bank accounts | 4 | A view, refreshed nightly |
| Old quarterly pack | Nothing anyone reads | 0 | Deleted |
The hours column is the one that does the work, and it has to come from the person who does the task, in their own measure. Not an industry benchmark, not our estimate. Three hours in a day, or four hours every Friday, or two hours a night after the kids are down. Owners can recite these numbers instantly, and once the numbers are on a page next to each other the order of work usually settles itself without an argument.
Where this goes
Counting the estate takes a working session with whoever holds the files and a few days of writing it up. What comes out is a ranked list with hours against every line, and a plain statement of which items are rules, which are connections and which are neither.
That document is the shape of how an engagement runs, and it belongs to the business that paid for it whether or not anything is built afterwards.
Put a number against every item on this list
The reporting diagnostic goes through the systems you already have and comes back with a scoped, priced plan from the team that would build it.
Book the reporting diagnostic