Strategy
Outgrowing a spreadsheet: five gaps that make it a system
Five gaps show what to replace, and one failure gives no warning.
Computing America. Applications, integrations, data analysis and AI/ML engineering.
Send the spreadsheet
In short
- A spreadsheet stops being a document and becomes a system when anyone besides its owner depends on it being correct. Several editors make it worse, and row count doesn't decide it.
- Concurrency, history, validation, referential integrity and access control are what a system provides and a spreadsheet leaves optional. A four-hundred-row file can lack all five.
- The failure that matters is silent. A spreadsheet has no constraint or change record, so a wrong number looks exactly like a right one until whoever acts on it finds out.
- A file format's capacity can drop data without a visible failure: in 2020 Public Health England's XLS templates, capped at about 65,000 rows, left nearly 16,000 positive Covid-19 results off its figures.
- Keep the spreadsheet where there is one owner, readers who only look, and a decision remade from scratch each time. Replace it where a wrong cell costs money quietly and you can't tell who typed it.
A system provides five things a spreadsheet leaves optional: concurrency control, history, validation, referential integrity and access control. Check which of them your file lacks and who relies on each; that decides which part to replace.
A spreadsheet is still a fast way to model a process that isn't fully specified. The danger is that the crossing happens without anyone deciding it, and the failure afterward is silent. Nothing constrains a value or records who set it, so a wrong number looks exactly like a right one until whoever acts on it finds out.
The threshold is who relies on it, not size¶
Everything that makes a system a system (transactions, history, validation, permissions) exists to answer one situation: people and processes touching the same data while others rely on it. A spreadsheet leaves each answer to habit, because it wasn't built for that. One person maintaining a file only they read can keep a hundred thousand rows in it and be fine.
Who relies on it being correct: Others and other processes or Only its owner
People editing it: One or Several
Others and other processes · One
Who relies on it being correct: Others and other processes. People editing it: One.
A system in hiding
- A script or colleague relies on a file one person maintains
Others and other processes · Several
Who relies on it being correct: Others and other processes. People editing it: Several.
A system: replace the part that needs it
- Several editors, others depend on the result
Only its owner · One
Who relies on it being correct: Only its owner. People editing it: One.
A document: keep it
- One owner, one reader
Only its owner · Several
Who relies on it being correct: Only its owner. People editing it: Several.
A shared scratchpad
- Several editors, nobody depends on it
Reliance, not editor count, makes a file a system: one person's file is one once a script or colleague depends on it.
SourceThe threshold defined in this article; no measured data.
The five gaps to check¶
Check the file against each row. Excel offers a partial answer to most of them, but each answer is optional, set by hand and not enforced by the file. The third column is what the gap looks like from the inside.
| Axis | What a spreadsheet gives you | What the gap looks like in practice |
|---|---|---|
| Concurrency | On a network share, Excel locks the file and offers the second person a read-only copy. Edits merge only with co-authoring on OneDrive or SharePoint, and even there the last saved change to a cell wins. Silent loss comes from copies saved over each other. | Work that was definitely done is missing a week later, because someone saved over a copy, and nobody can settle who did. |
| History | Microsoft 365 has Show Changes and Version History, but the Changes pane covers only recent edits and can have gaps. Nothing keeps a complete, enforced record of who set a value. | A number is wrong, nobody can learn when it became wrong, and the only remedy is to re-derive everything downstream. |
| Validation | Data Validation can restrict a cell to a type or a list, but someone has to set it on every cell. Where nobody has, a date column takes “ASAP” and a quantity takes “see Dave”. | Totals that look fine and are wrong, 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 using the old spelling returns an error or, worse, a zero. |
| Access control | Permissions attach to the file. Sharing it shares every sheet, row and column in it. | People who shouldn't see margin or salary can, or half the team is locked out and mails changes to whoever holds the file. |
The limit nobody hits until they do¶
A modern Excel worksheet holds 1,048,576 rows by 16,384 columns (opens in a new tab), more headroom than most operations will ever need. The trouble is what happens at a ceiling. In 2020, Public Health England left nearly 16,000 positive Covid-19 test results (opens in a new tab) out of its figures because an automated process pulled lab files into templates in the older XLS format, which handled about 65,000 rows. Once a template filled, BBC reported, further cases were left off, and contact tracers saw those people's details late.
When to keep it¶
A spreadsheet is the right tool, and replacing it wastes money, when:
- One person owns it, and the people who read it only look: no script or report consumes it, and none of them depends on it being right.
- The process it models is still changing shape month to month. Software is a commitment to a shape.
- The decision it supports is remade from scratch each time. A model you rebuild for each bid has no past to keep.
- It's wrong sometimes and being wrong is cheap, because a person reads the output and would notice.
- It's temporary, and you can name the date you'll retire it.
When it has already stopped being one¶
- Part of somebody's job is maintaining it, and that isn't written down anywhere.
- There's 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 travels without a person in between.
- A customer, a regulator or an auditor has asked a question the file can't answer, because answering it needs to know when a value changed.
- People keep their own side copies, so the organization has more than one answer to the same question.
- You can't take a vacation without briefing somebody on the file.
None of this means a large project. The part that needs concurrency, history and validation may be one table in a workbook of many tabs, and the rest can stay where it is. Settle that boundary before anybody writes code.
Before you price a replacement, answer one question: what does a wrong cell cost? If none of the signs above apply and you can't name that number, find it first: ask the last person who found a wrong value what it took to fix and how often it happens, and multiply the two. If any apply, scope the one table that needs history and validation.
Sources
Next step
Send us the spreadsheet.
Send the workbook, or a screenshot with the numbers changed. We will say which of the lines it has actually crossed, and which columns would have to become a real record first.
- Phone
- (214) 723-2510
- Reply
- A person replies, not a sequence: within one business day, from someone who would be on the engagement.
Packages
Start with a package
Four packages, each with its price on the page. Pick one and buy it online.
$199/month
Billed monthly
$182.42/month
Billed yearly
$499/month
Billed monthly
$457.42/month
Billed yearly
- Scammers stopped from sending email as you
- Your website and systems scanned every week
- Your email settings checked every week
- Everything in the 1 to 10 plan
- Covers up to 50 people
$199/month
after a one-time setup fee
- A site planned, designed and built for you
- Hosting and updates every month
$499/month
Billed monthly
$457.42/month
Billed yearly
$999/month
Billed monthly
$915.75/month
Billed yearly
- Your Google profile, fully set up
- A Google post every week
- 2 social posts a week
- Everything in Get found
- 4 social posts a week
- Your top 10 Google searches tracked
from$500/month
after a one-time setup fee
- Your data gathered from the tools you use
- A first analysis that answers your questions
- New findings every month
Related reading
Strategy3 min readBuild vs buy software: sort each process step before you write requirements
Sort every process step as commodity, differentiating or contextual before anyone writes a requirements document.
Data2 min readWhy departments report different revenue numbers, and how to fix it
The meeting should be about the decision, not about whose number is right. Write each metric definition once and make every report read it.
Strategy3 min readLow-code limits: where the ceiling is, and the signs you are near it
Four constraints set a low-code platform's ceiling, and the signs you are near it are visible.
