7 signs your company has outgrown Excel

There’s nothing wrong with Excel. For calculations, analysis and one-off reports, it’s hard to beat. The trouble starts when a spreadsheet quietly turns into a company system. Suddenly it holds order tracking, inventory, attendance or a production plan, and daily work depends on every row.

How to tell it’s time for a change

1. Several people work in one file. They overwrite each other, the file is “locked by another user”, and copies named “final_v2” appear.
2. You retype data by hand. An order from email into a spreadsheet, from the spreadsheet into accounting, from accounting into a report. Every retype costs time and invites mistakes.
3. Nobody knows who changed what. When a number changes, there’s no way to find out who, when and why. With money, stock or contracts, that’s a risk.
4. Only one person understands the spreadsheet. The formulas and macros are known only to whoever wrote them. When they leave or get sick, work stops.
5. The file is slow and crashes. Tens of thousands of rows, linked sheets and macros. It takes a minute to open and occasionally gets corrupted.
6. There are no permissions. A sales rep sees margins they shouldn’t, and anyone can delete a column by accident.
7. Reports are built by hand. Every month someone spends hours copying data so management gets numbers that could have been available any time.

When a spreadsheet fails: four documented cases

Spreadsheet errors aren’t a theory. These cases are publicly documented and show exactly where the weak spot is.

• UK public health, 2020. Public Health England admitted that 15,841 positive COVID-19 tests from 25 September to 2 October were missing from the daily figures. According to the BBC, lab results were converted into templates in the old .xls format, which is limited to 65,536 rows. Each test took up several rows, so a template only fit about 1,400 cases and the rest were silently left off. Contact tracing was delayed by several days.
• JPMorgan, 2012. The model the bank used to measure risk ran in a series of spreadsheets, with data copied between them by hand. According to the internal review, one formula divided by a sum instead of an average, which muted market volatility by roughly half. The model therefore showed less risk than there really was. The loss of over six billion dollars was caused by a trading strategy, but the faulty spreadsheet helped hide the risk.
• An influential study on public debt, 2013. Three economists from the University of Massachusetts found that a formula in Reinhart and Rogoff’s spreadsheet left out five countries. The error itself explained only a small part of the difference in results, but it publicly undermined the study and the authors acknowledged it.
• Genetics, 2016 to 2021. Excel automatically converts gene names such as SEPT1 or MARCH1 into dates. A 2016 study found such errors in a fifth of scientific papers with gene lists in Excel supplements. A follow-up study in 2021 found them in almost a third. The human gene naming committee renamed these genes in 2020, for example to SEPTIN1 and MARCHF1.

Audits of business spreadsheets keep finding errors. Raymond Panko, who has studied spreadsheet errors for decades, reports errors in 86 to 94 % of audited spreadsheets. The samples are small, though, and other researchers criticise these figures. What can safely be said is that the larger a spreadsheet gets and the more people touch it, the more likely an error becomes.

In none of these cases did Excel fail as a program. The process failed: manual copying between files, formulas nobody checked, and a tool used for work it wasn’t built for. Those are exactly the signs listed above.

Where to start

You don’t have to replace everything at once. Pick the one spreadsheet that costs you the most time or causes the most mistakes. It’s often order, inventory or asset tracking. For that one, you have three options:

• A shared cloud spreadsheet (Google Sheets, Excel in Microsoft 365). It solves people working at the same time, but not permissions, change history or automation.
• An off-the-shelf app. If your area follows a common standard, such as CRM, accounting or attendance, it’s the fastest route.
• Custom software. It pays off when your company works differently from others, when you need several systems connected, or when you pay for features you don’t use.

Spreadsheet data can be migrated into a new system. Expect the migration to reveal how inconsistent the data really is. Cleaning it up is a benefit in itself.

Sources

1. Public Health England: statement on delayed reporting of COVID-19 cases
2. BBC, Leo Kelion: why the test results were lost (5 October 2020)
3. Report of the JPMorgan Chase Management Task Force regarding 2012 CIO losses
4. US Senate Permanent Subcommittee on Investigations: JPMorgan Chase Whale Trades
5. Herndon, Ash, Pollin: Does High Public Debt Consistently Stifle Economic Growth? (2013)
6. Ziemann, Eren, El-Osta: Gene name errors are widespread in the scientific literature, Genome Biology 2016
7. Abeysooriya et al., PLOS Computational Biology 2021
8. Bruford et al.: Guidelines for human gene nomenclature, Nature Genetics 2020
9. Raymond Panko, EuSpRIG proceedings 2015
10. Powell, Baker, Lawson: critical review of spreadsheet error estimates, Decision Support Systems 2008