Replacing the spreadsheet only one person understands

Almost every established business has one. A workbook that schedules the depots, prices the quotes, drives the reorder list or closes the month. It has been extended for years, it is central to the operation, and one person genuinely understands it. When that person is on leave, something important waits.

The instinct is to treat it as a mess to be cleaned up. That instinct is what causes the replacement to fail.

The spreadsheet won, and that is worth respecting

It is in production. It is trusted. It survived contact with the real process, which is more than can be said for most software. Whoever built it encoded years of operational knowledge without a budget, a project manager or a requirements document, and the business ran on the result.

None of that means it is safe. Reviews of operational spreadsheets have repeatedly found error rates high enough to be uncomfortable: Raymond Panko’s survey of field audits reported that around 94 per cent of the spreadsheets examined contained at least one error, at roughly a five per cent cell error rate. But error rate is rarely why a business finally decides to replace one, and leading with it usually just insults the person you most need on your side.

What you are actually replacing

The formulas are not the specification. They are the part of the specification that happened to be written down.

The rest lives in the head of whoever runs it, and it is the part that makes the output correct. It usually looks like this:

• A manual override applied by eye — a figure that gets adjusted before the file goes out, according to a rule nobody has ever stated aloud
• Conditional behaviour that only fires at month end, at year end, or for the two suppliers who are handled differently
• Silent data cleaning on paste — trimmed names, corrected product codes, variants mapped to the right account
• A reconciliation against another system that is genuinely load-bearing, and was never mentioned because it is not part of "the spreadsheet"

A new system built from the visible formulas will be wrong in ways that take weeks to surface, because the people checking it will assume the software is right and the discrepancy is theirs.

The formulas are the part of the specification that happened to get written down. The part that makes the output correct is usually in someone’s head.

The trigger is key-person risk, not accuracy

What actually forces the decision is that one person can change a system the business depends on, and nobody else can verify what it does. There is no review, no test, no history of who altered which rule and why. The organisation is carrying an operational dependency it cannot inspect.

The danger in that is not loud failure. It is silent failure. In 2020, Public Health England lost 15,841 positive COVID-19 test results because the workbook collecting them was in the legacy .xls format, which stops at 65,536 rows. Nothing raised an alarm. Rows past the limit were simply dropped, and the cases reached contact tracers days late. The system did not report that it had failed, because as far as it was concerned nothing had gone wrong.

That is the characteristic failure of a critical spreadsheet. It does not announce the problem. It hands you a number that looks like every other number it has ever produced.

Why the replacement costs more than the build

Here is the part clients do not expect: writing the software is the cheap half. Working out what the spreadsheet actually does is the expensive, unavoidable half, and it cannot be done without sustained access to the person who built it.

That means real hours from your most capable and least available person, sitting with an engineer, walking through why that column exists. Any plan that does not budget for their time is not a plan. When a project like this runs over, the cause is almost never the engineering — it is that the specification was discovered in instalments, each one arriving after something had already been built the wrong way.

What moves the number, roughly in order of impact:

1. How much of the logic is undocumented, and whether its author is available and willing
2. How many systems it reads from or writes to, and whether any of them have a usable interface
3. How many people touch it, because every additional user has a habit that turns out to be a requirement
4. Whether the business agrees on the rules, or whether the spreadsheet has been quietly arbitrating a disagreement

Notice that only the second of those is a technical question.

Rebuild less than it does

A workbook that has been extended for a decade is part live system and part archaeology: abandoned experiments, columns kept in case someone asks, a tab that was superseded two years ago and never deleted. Porting all of it faithfully moves the mess into something considerably harder to change.

Go the other way. Establish which outputs the business actually depends on, build those properly, and let the rest stay in a read-only archive copy. It is a smaller job and a better system, and it is usually the point at which someone realises two of the reports have not been opened since 2023.

Run both until the numbers match

Do not switch the spreadsheet off on go-live. Run the two in parallel over a full cycle — a complete month if it is a month-end process — and reconcile the outputs every time. Every discrepancy is either a bug or an undocumented rule you have just found, and both are worth far more before the cutover than after it.

Agree in advance who signs off the switch and what has to be true for them to sign. "It looked fine" is not a criterion. "Three consecutive runs matched to the penny, and the two variances we found were explained" is.

When to leave it alone

Sometimes the honest recommendation is not to build anything.

If the spreadsheet is stable, rarely changes, and the genuine risk is that one person holds it, then the problem is not the tool. Version history, a real backup, documentation of the non-obvious rules and a second person trained to run it will remove most of that risk for a small fraction of what a rebuild costs. That is a sensible outcome and it should be offered.

Two other cases argue for waiting. If a reorganisation, a system migration or a regulatory change is coming, encoding today’s rules in software now means rebuilding them shortly afterwards. And if the process is genuinely low volume — a handful of people, a monthly run — a spreadsheet may simply be the right tool, and replacing it would make everyone’s job worse.

There is one case where we would decline the work outright: when nobody can explain the rules and nobody will sit down to work them out. That is not a difficult project, it is a project with no specification, and it will fail regardless of who takes it on. Better to say so at the start than to discover it in month four.

The question worth asking first

Before anyone scopes a replacement, ask what happens if the person who maintains it leaves this month. If the answer is vague, that is the actual problem, and it is worth being clear that software is only one of the ways to solve it.

Common questions

Written by the Voxalone team. If this raised a question about your own systems, get in touch — you will get a straight answer from an engineer, with no obligation attached.