When a spreadsheet has outgrown itself: a test you can run this afternoon

The honest half: most spreadsheets should stay spreadsheets

A spreadsheet is the fastest modelling tool most businesses own. Nothing else lets one person restructure a problem in ten minutes without asking permission, and that flexibility is the first thing you lose when you build software. Leave the file alone if it matches any of these:

• One person uses it to think. Scenario models, price tests, a what-if on headcount. The value is in being able to tear it up; building software around exploratory work pays to make it rigid.
• It runs a handful of times a year. An annual budget sheet or a one-off tender comparison does not justify a system.
• The structure changes every time you open it. If each use needs different columns, you do not have a process yet — you have an activity. Wait until it settles.

The ones worth worrying about are the opposite: several people depend on them, they run on a schedule, they hold the only copy of something, and other work queues behind them.

Test 1 — How many people need it at once?

Count two things: how many people edit the file in a typical month, and how many need it open at once.

• One editor: fine on this measure, whatever else is true.
• Two or three, rarely at once: manageable. Shared cloud editing in Microsoft 365 or Google Sheets handles it, if you use it properly — one file, one location, no emailed copies.
• Four or more, or any regular need for two people in it simultaneously: the file is being used as a database by people who have not been given a database. This is where "final_v3_JK_updated" appears.

The threshold that matters is the collision rate, not the headcount. If anyone has lost work to a version conflict in the last six months, this test is failed however short the list is.

Test 2 — Can you prove who changed what, and when?

Open the file and try to answer three questions about any number in it: who changed it, when, and what was it before?

Excel and Google Sheets version history answer that partially, at the file level, which beats nothing. What they do not give you is a per-record trail that survives a restructure, a reason for the change, or a signature against it.

You need a real trail if the file touches money leaving the business (pay rates, approvals, credit limits, supplier terms), safety or licensing (inductions, SWMS, permits, plant certificates, insurances), or anything a third party can demand to see — an auditor, an insurer, a principal contractor, a large customer running a supplier audit.

If someone outside the business could reasonably ask "show me the history of this record" and your honest answer is "I would reconstruct it from emails", the test is failed. Compliance registers fail it more often than anything else, which is why they are usually the first thing worth moving. Our page on replacing spreadsheets with a purpose-built system covers what a register looks like done properly.

Test 3 — What happens when the owner goes on leave?

Every serious spreadsheet has an owner, whether or not anyone has said so. Ask that person directly: if a change is needed while you are away, who makes it, and if nobody can, what stops?

An answer in the shape of "someone else can do it" means the file is simple enough, or documented well enough. An answer in the shape of "it would wait until I got back", or "nobody else knows why that tab exists", means the business is carrying key-person risk inside a file — an exposure that appears on no risk register, because nobody thinks of a spreadsheet as a system.

A sharper version of the same question: has anyone ever delayed taking leave because of this file? If so, the test is failed and you already knew.

Test 4 — How long does a change take?

Time the last three changes. Not building the file — a normal change. Adding a site, changing a pay rule, adding a product category, adjusting a margin threshold.

• Minutes, by the person who needs it: healthy. The spreadsheet doing its best work.
• A few hours, by the only person who understands it: workable now, fragile later.
• Days, or a change nobody will attempt for fear of what it breaks: the file has stopped being editable. When people are afraid to change a tool, the process stops adapting to the business, and the business starts adapting to the spreadsheet.

That last state is the one people misdiagnose. It does not feel like a crisis, it feels like mild annoyance. The real cost is that sensible operational changes — a new depot, a customer with different terms, a different shift pattern — get quietly ruled out because the file cannot cope.

Test 5 — What does one mistake cost?

Finally, price one realistic error — not a catastrophe, an ordinary one that has happened or plausibly could. Work it through with numbers you have:

• A quote sent below margin because someone copied last month's tab and missed an updated cost line. What is the gap, and how often would nobody catch it?
• A sum range that stopped extending when rows were added, so a month of costs was under-reported. How long until someone noticed, and what was decided on the wrong number in between?
• An expiry missed on an induction or a licence, so somebody works a shift uncertified. What does that cost in stand-down time and in the client relationship?

Multiply one occurrence by a conservative guess at frequency. If the annual figure is smaller than the cost of building something, you have your answer, and it is a legitimate one. Files that are mildly wrong, rarely, are not worth replacing. The ones worth replacing are those where a single quiet error costs more than a year of the alternative.

Scoring it, and what to do next

Count the failures.

Nought or one failed. Keep it. Fix the single weakness — usually one cloud-hosted copy instead of several, protected formula cells, and half a page describing how it works so the knowledge is not held in one head.

Two or three failed. Do not go straight to custom software. The middle options earn their keep here and cost far less. Separate data entry from reporting. Lock formulas and use validation so free text cannot break a lookup. Move the data into something with row-level structure — Microsoft Lists, a Power App over SharePoint, Airtable — and keep the analysis in Excel on top. Or check whether the system you already pay for does the job natively: Xero, MYOB, Cin7, Deputy, SimPRO and Employment Hero all carry features businesses have rebuilt in spreadsheets because nobody had time to configure them.

Four or five failed. The spreadsheet is a system now, and one nobody maintains, secures or backs up on purpose. Here building something for the process is proportionate — particularly if the failures include Tests 2 and 5 together, the combination that turns an annoyance into a liability.

On sequencing: if several files fail at once, start with the one where a mistake costs most, not the one that annoys people most. They are rarely the same file, and the annoying one often resolves itself once the data underneath is structured — which is also when reporting and dashboards stop being a monthly assembly job.