Skip to content

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
4 min readComputing America
A printed spreadsheet on a warehouse office wall, covered in handwritten corrections and sticky notes.

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.

Fig. 1

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

A system in hiding

  • A script or colleague relies on a file one person maintains

Others and other processes · Several

A system: replace the part that needs it

  • Several editors, others depend on the result

Only its owner · One

A document: keep it

  • One owner, one reader

Only its owner · 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.

AxisWhat a spreadsheet gives youWhat the gap looks like in practice
ConcurrencyOn 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.
HistoryMicrosoft 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.
ValidationData 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 integrityLookups 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 controlPermissions 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.
What Excel offers for the five properties a system guarantees, and why the offer isn't a guarantee.

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

  1. 1.Excel specifications and limits (opens in a new tab), Microsoft
  2. 2.Excel: Why using Microsoft's tool caused Covid-19 results to be lost (opens in a new tab), BBC News,

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.

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.

Billing