Strategy
When a spreadsheet becomes a system
Most operations run on a spreadsheet, and most of them should carry on doing it. But there is a real line, it is not where people expect, and crossing it is not obvious from the inside.
In short
- A spreadsheet stops being a document and becomes a system the moment somebody depends on it being correct while somebody else is editing it. The threshold is concurrency, not size.
- Row count is the weakest of the signals. Concurrency, history, validation, referential integrity and access control are the five that decide it, and a four-hundred-row file can fail all five.
- The failure mode that matters is silent rather than loud. A spreadsheet has no transaction and no audit trail, so a wrong number propagates looking exactly like a right one, and is found downstream by whoever acts on it.
- Exceeding a file format's capacity truncates without raising an error: Public Health England lost 15,841 positive Covid-19 tests in 2020 to an XLS row limit, with every process around it reporting success.
- Keep the spreadsheet where there is one owner, one reader, and a decision remade from scratch each time. Replace it where a wrong cell costs money quietly and nobody can say who typed it.
Almost every operations business we talk to runs something important on a spreadsheet. Scheduling, inventory, job costing, who is on site this week, which units shipped and which are still on the floor. This is usually described to us apologetically, and it should not be. A spreadsheet is the fastest way ever built to model a process nobody has fully specified yet, and a business that got to where it is on one has been served well by it.
The useful question is not whether the spreadsheet is embarrassing. It is whether it is still a document (something a person opens, reads and decides from) or whether it has quietly become a system, something other people and other processes now depend on being correct. Those are different objects with different failure modes, and the transition happens without anybody deciding it.
The threshold is concurrency, not size¶
The question we are asked most often is how many rows is too many. It is the wrong axis, and it is the reason the line gets crossed unnoticed, a file grows gradually and nothing about row two thousand feels different from row two hundred.
The line is this: a spreadsheet becomes a system the moment somebody depends on it being correct while somebody else is editing it. Everything that makes a system a system (transactions, history, validation, permissions) exists to answer that one situation. A spreadsheet answers it with last-write-wins and no record, because it was never asked to answer it at all. One person maintaining a file that only they read can keep a hundred thousand rows in it indefinitely and be entirely fine. Four people maintaining a four-hundred-row file are already past the line.
The five axes that actually decide it¶
Take these one at a time against the file you are thinking of. The count that matters is how many you fail, not how large it has got.
| Axis | What a spreadsheet gives you | What its absence looks like in practice |
|---|---|---|
| Concurrency | Last write wins, silently. Two people with the file open produce one survivor and no warning. | Work that was definitely done is missing a week later, and the argument about who deleted it cannot be settled. |
| History | Nothing, beyond whatever version the storage layer happens to keep. There is no per-cell record of who set a value or when. | A number is wrong and there is no way to learn when it became wrong, so the only remedy is to re-derive everything downstream of it. |
| Validation | A cell accepts anything. A date column takes “ASAP”, a quantity takes “see Dave”. | Totals that are quietly wrong rather than visibly broken, and a monthly ritual of cleaning the file before it can be reported from. |
| Referential integrity | Lookups match on text. Nothing connects a row here to the row it refers to over there. | A customer is renamed in one place, and every formula referencing the old spelling returns an error or, worse, a zero. |
| Access control | All or nothing. Sharing the file shares every row and column in it. | Either people who should not see margin or salary can see them, or half the team is locked out and mails changes to whoever holds the file. |
Failing one of these is normal and usually survivable with a habit, a naming convention, a rule that only one person edits on Fridays. Failing three or more is a system pretending to be a document, and the habits holding it together are load-bearing and undocumented.
The limit nobody hits until they do¶
Size does eventually become real, and it is worth knowing where the wall is. A modern Excel worksheet holds 1,048,576 rows by 16,384 columns (opens in a new tab), which sounds like more headroom than an operations business will ever need, and usually is.
The problem is not the ceiling. It is what happens at it. In 2020, Public Health England lost 15,841 positive Covid-19 test results (opens in a new tab) because an automated process wrote into the older XLS format, whose limit is 65,536 rows rather than a million. Nothing errored. Every step reported success. The rows past the limit were simply not there, and the people who would have been contacted were not contacted.
When to keep it¶
We would rather say this plainly than have somebody spend money proving it. A spreadsheet is the correct tool, and replacing it would be a waste, when:
- One person owns it and the people who read it do not edit it.
- The process it models is still changing shape month to month. Software is a commitment to a shape, and committing early to the wrong one is more expensive than any spreadsheet.
- The decision it supports is remade from scratch each time rather than accumulated. A model you rebuild for each bid does not need history because it has no past worth keeping.
- It is wrong sometimes and being wrong is cheap, because a person reads the output and would notice.
- It is genuinely temporary, and it is worth being honest with yourself here, because most of the files we are eventually asked to replace were temporary.
When it has already stopped being one¶
The clearest signals are not about the file. They are about what has grown up around it:
- Somebody's job is partly to maintain it, and that has never been written down anywhere.
- There is a copy called final, a copy called final-v2, and disagreement about which is authoritative.
- Another process reads it automatically (a script, an import, a report) so a wrong cell now travels without a person in between.
- A customer, a regulator or an auditor has asked a question that the file cannot answer, because answering it needs to know when a value changed.
- People have started keeping their own side copies, which is the point at which the organisation has more than one answer to the same question.
- You cannot take a holiday without briefing somebody on the file.
None of these means the answer is a large project. The usual first step is much smaller than people expect: the part that needs concurrency, history and validation is often one table out of eleven tabs, and the rest can stay exactly where it is. Getting that boundary right is most of the value, and it is a question worth answering before anybody writes anything.
What we would say against doing it at all: if you cannot name what a wrong cell costs, you are not ready to price the replacement, and the honest thing is to keep the spreadsheet and find that number first.
Sources
Related reading
- Strategy4 min read
Build versus buy: the only test that actually settles it
Most build-or-buy debates are decided by whoever presents last. There is a better question, and it takes about ten minutes to answer.
- Data3 min read
The three-numbers meeting, and how to end it
When finance, sales and operations bring different figures for the same month, the problem is never the dashboard tool.
Next step
Tell us what’s breaking.
Forty-five minutes, no charge, no deck. We’ll tell you what we’d do, what it would likely cost, and whether what you already have can be made to work.