When a growing business outgrows its spreadsheets

Almost every successful small business is built, in its early years, on spreadsheets. They are flexible, familiar and free of procurement. The founder can change a formula at midnight. Nothing could be better for learning how a business works.

The difficulty arrives later and quietly. The spreadsheet that once tracked twenty orders now tracks two thousand. Five people edit it. A new hire cannot tell which tab is current. The business has begun to depend on a file.

What the research says about spreadsheet errors

Researchers have studied spreadsheet errors for decades. Professor Raymond Panko, a leading scholar in the field, compiled the results of experiments and audits in a widely cited review. His table of experiments covers 1,170 spreadsheets built by 998 participants. The weighted average share containing at least one error was 51%, and per-study cell error rates ranged from roughly 1.0% to 5.6%. In one student experiment, 86% of spreadsheets contained at least one error.

Panko's review also reports an industry survey of financial models by KPMG, described in a 2000 Internal Auditing article by D. Chadwick. It found major errors in 95% of the models reviewed, and poor design in 59%.

A very public failure

In October 2020, Public Health England reported that it had missed 15,841 positive COVID-19 test results between 25 September and 2 October. According to coverage of the BBC's report in Information Age, the agency was using the older .xls file format, which holds about 65,000 rows, rather than the newer format, which holds more than a million. Records beyond the limit were not carried over, so cases were not passed on promptly to contact tracers. The health minister, Matt Hancock, told the House of Commons that the incident should never have happened.

The lesson is not that spreadsheets are dangerous. It is that a spreadsheet silently doing the job of a system, with no validation, no audit trail and no alert when something fails, will fail in exactly this way: quietly, and at the worst moment.

Seven signs you have outgrown them

• Version confusion. People ask which file is the latest, or work from copies sent by email.
• Single point of failure. One person understands the formulas, and the business stops when they are away.
• Endless reconciliation. Two files that should agree never do, and finance spends days finding the difference.
• Re-keying. The same data is typed into the spreadsheet, the accounting package and a customer email.
• No audit trail. Nobody can say who changed a price or a quantity, or when.
• Slow to open and easy to break. The file takes minutes to load, and one wrong paste breaks it.
• Controls by trust. Approvals and limits exist only because people remember to follow them.

Two or three of these are an inconvenience. Five or more mean the file has become infrastructure, and it needs to be treated as such.

How to move without making things worse

Start with the process, not the software

Write down how an order, a purchase or a stock movement should flow, who approves what and what must never be wrong. Systems fail in small organisations largely because they are bought before this step. Our earlier articles on systems modernisation and on fixing the process before automating it go deeper.

Move the record first; leave the analysis

The things that need accuracy, access control and history, such as customers, orders, stock, purchases and payments, belong in a system. Spreadsheets remain excellent for modelling, one-off analysis and quick what-if work. Exporting data from a system into a spreadsheet for analysis is healthy. Typing data into a spreadsheet to run the business is the risk.

Choose proportionately

A business with fifteen staff does not need the same system as a bank. The right choice may be a well-configured cloud accounting and inventory package, or a modular ERP that grows with you. What matters is a single source of truth, role-based access, an audit trail and the ability to produce the monthly numbers without re-keying.

Run in parallel, then cut over

Migrate clean data, not everything. Run the old and new side by side for one full cycle, reconcile every difference, and switch off the old file by a stated date. A spreadsheet left running "just in case" becomes the real system again within a quarter.

Train the people who will live in it

A system fails when staff keep a private spreadsheet because they do not trust it or do not know how to use it. Invest in training and in a named owner for the data.

Moving off spreadsheets is not a technology milestone. It is the point at which a business decides that its operations deserve the same care as its products. Done in proportion, it is also one of the most reliable ways to free the founder from being the only person who knows how the business works.