When Excel is no longer enough: moving to a business application

Excel isn't the problem. It is probably the most useful tool ever put in a business owner's hands: free or nearly, immediate, no training required. Most of the companies I meet started out running on a spreadsheet, and they were right to.

So the real question isn't "should we leave Excel", but: at what point does a spreadsheet stop being a tool and become a risk? And above all, how do you recognise that moment before the incident rather than after?

A spreadsheet doesn't warn you when it reaches its limit. It just carries on.

The story that should make us all think

Autumn 2020, United Kingdom. The English public health agency was centralising Covid test results in an Excel file. Between 25 September and 2 October, 15,841 positive cases vanished outright from the national count. Thousands of contagious people were never contacted.

The cause was neither a hack nor an outage: the file had been saved in an older Excel format, capped at around 65,000 rows. Once that ceiling was reached, new results didn't trigger an error. They were simply ignored, silently. Nobody was told.

That is exactly a spreadsheet's blind spot, and it has nothing to do with the skill of the person using it. A well-built business application rejects an impossible value and shows a message. A spreadsheet accepts everything and says nothing.

What research says about spreadsheet errors

The academic work of Raymond Panko, who compiled thirteen studies auditing spreadsheets actually in use in businesses, reaches a figure that is hard to ignore: 94% of the files audited contained at least one error. On average, around 5% of the cells checked were wrong.

The most uncomfortable detail lies elsewhere. That research compared beginners with experienced users having more than 250 hours of practice: the difference in error rates was not significant. In other words, this isn't a training problem. It is the nature of the tool.

And it doesn't only affect small organisations. At JPMorgan in 2012, a chain of risk models kept in Excel — with manual copy-pasting from one file to the next, and a formula dividing by a sum instead of an average — contributed to a loss of more than 6 billion dollars. The bank's own internal investigation documents it.

The seven signals that the tipping point has come

If three of these apply to you, you are no longer using a spreadsheet: you are maintaining software without knowing it.

• Several people edit the same file, and you don't always know who changed what, or when.
• There are files called "final", "final_v2" and "final_fixed", and nobody is certain which one is right.
• The same information is entered twice, in two different tools, by two different people.
• Only one person really understands the file, and your summer holidays depend on their availability.
• You can't answer a simple question in thirty seconds, such as revenue per customer over the last six months.
• The file holds personal data about customers or staff, and it travels by email.
• If that file disappeared today, your business would be seriously disrupted.

What an application does that a spreadsheet never will

The difference isn't cosmetic. It comes down to five things a spreadsheet structurally cannot guarantee.

• It refuses impossible data: an end date before the start date, negative stock, a customer who doesn't exist. The check sits in the tool, not in the user's vigilance.
• It handles several people at once, with no overwriting and no competing versions.
• It keeps the history: who changed what, when, and what the value was before.
• It gives each person what concerns them: your accountant, your sales rep and your technician don't see the same information.
• It connects to your other tools — invoicing, accounting, email — to eliminate double entry.

The mistake to avoid: replacing everything at once

This is the most natural reflex and the most expensive one. Trying to replace twelve files with one complete application in a single go produces a long, costly project that is often abandoned before it is ever used.

The approach that works is the opposite: you take the single process that hurts most, the one that wastes time every week or has already caused a mistake a customer noticed. You take it out of the spreadsheet, put it into service, and measure. The other files stay where they are as long as they do the job — and many do it very well.

One important point: a custom application isn't always the right answer. Sometimes an existing product at a few tens of euros a month covers 90% of the need. An honest professional will tell you that before billing you for development, and that is what I do too.

Where to start, concretely

Before even discussing budget, take a sheet of paper and write down three things: the process that costs you the most time each week, how many people touch the file involved, and what would happen if it disappeared tomorrow. Those three answers are enough to know whether to act now or wait.

If you want an outside opinion on your situation, a focused audit of your files and processes can settle the question in a few days, without committing to a full project. It is usually the cheapest way to discover that development wasn't necessary — or that it has become urgent.

Sources

• Raymond Panko — Spreadsheet Errors: What We Know, What We Think We Can Do
• Panko — What We Don't Know About Spreadsheet Errors Today (audit review)
• The Register — Excel error blamed after England under-reports 16,000 COVID-19 cases
• PublicTechnology — Lost data on 16,000 coronavirus cases pinned on Excel
• Microsoft — Excel specifications and limits
• Forbes — Solutions to spreadsheet risk post JPMorgan's London Whale