When the UK government announced 16,000 COVID infections had been missed because of a technology error, people were rightly upset. But this is just the latest in a long history of operational failure due to an over-reliance on Excel, causing weak-link fracture of vital business processes.
The wrong tool for a vital task
UK government attempts to control the spread of COVID-19 were dealt a serious blow after officials were forced to revise the official infection count. Overnight, the total number of infections increased by 16,000 because of a “technical issue”.
As the news broke it quickly became apparent that the miscount had been caused by the most basic of data architecture mistakes - using older XLS Excel templates to capture data for the infection tracking service. As the number of infections rose across the UK, the legacy format templates hit the maximum number of rows and stopped recording new data input.
As the story developed, dozens of IT experts and government officials were quoted expressing their incredulity at the use of Excel for a critically important disease-tracking system. But for all the outrage, this is a problem that continues to be repeated throughout public and private organisations year after year after year.
A public sector disaster in waiting
UK energy regulator Ofgem found themselves in a similar situation. The organisation was trying to provide oversight of an £80 billion industry using a single Excel spreadsheet. Composed of 250 sheets with 1 million lines per sheet, the document was a disaster waiting to happen – particularly as there was minimal version control as the document was constantly circulating by internal email between 900 staff.
Eventually management became aware that the spreadsheet was a major cause of operational failure. Loss, corruption, version control issues and the technical limitations of Excel were limiting productivity and wasting budget.
When senior management decided to replace the spreadsheet with a fit-for-purpose data platform, all of these issues could be addressed and overcome. Indeed, Ofgem is now regarded as a beacon for other government bodies – so why do other bodies like the National Audit Office and Public Health England persist with Excel which they know is unsuitable for any enterprise-class process.
Real talk – This is going to happen again. And again. And again.
Behind the scenes, government agencies and their critical operational processes are hugely reliant on Excel. The problem is most don’t realise they are sitting on a ticking timebomb.
The fact processes creak along creates a false sense of security. But these operational processes are brittle, ready to fail spectacularly at any moment. And the warning signs are there – if government decision makers are willing to look (most aren’t).
Excel is undeniably feature-packed – and that’s not necessarily a good thing. Conceptually, pivot tables and lookups are a great way of extracting and manipulating data from other spreadsheets or databases. But there is a cost – inefficiency. The more complex the spreadsheet, the more likely it is to crash, leading to potential data loss and corruption, and the embarrassment of becoming the next government department to suffer an (avoidable) disaster.
Almost inevitably, complex spreadsheets have been built by a team member. Data operates in silos and calculations applied by the spreadsheet are not documented in Excel. However, so what happens when an individual is reassigned, resigns or retires? Someone will need to reverse-engineer the spreadsheet. An enterprise IT system not only collects data and performs calculations, it also allows you to document every treatment applied to your data, mitigating the risk of your data specialist leaving.
Catastrophic operational failure
Excel is not an enterprise-grade data technology, making it unsuitable for those operations. Collapse of the system is almost inevitable – in the case of the Covid reporting tool because a technical limit was reached. Another common cause of failure is someone over-typing data, or even accidentally deleting the file. The spreadsheet file is simply too “brittle” for enterprise operations.
Aside from cell types, Excel offers little by way of data validation. This means users can (and do) enter junk. There is no change control on treatment done to data in the spreadsheets so no one knows the real truth. After a while, no one even knows if the data is actually accurate. Imagine being the minister facing select committee scrutiny, trying to defend data and having no one in your department who can verify it is completely true.
What can we learn from the Covid spreadsheet incident?
At the heart of these problems is the use of Excel spreadsheets for mission-critical operations - but how did this happen? By using standalone Excel for mission-critical operations rather than true enterprise tooling.
By expecting employees to perform data transformation and analytics with the wrong tools, government agencies (and any other business making the same mistake) are inviting disaster. Excel is fine for data enthusiasts, but when making life-changing strategic decisions, you must use enterprise scale tools.
Excel is a great tool and many people have learned how to use it to get results quickly. To avoid a repeat of the Covid spreadsheet incident, you should restrict the use of Excel for prototyping. You can define what your new system must be capable of, then allow your IT department to specify a true enterprise data tool to suit.
Managers and decision makers must regain control of their data – or risk potential disaster. Replacing Excel with true enterprise data tooling must be a strategic priority, allowing your organisation to make better use of its data – and avoid becoming the next high-profile disaster.
The Covid spreadsheet incident is a huge concern for every government department to audit their business processes and identify where over-reliance on Excel is a problem. Any mission-critical activity which is run using Excel (or any other consumer-grade application) is a risk and an accurate automated solution is required.
Dan is a leader in Zühlke's Data and AI practice and has extensive experience working across a diverse range of sectors, including government, transport, telecoms, and manufacturing. As a skilled engineer and strategic advisor, Dan effectively connects the needs of leadership with the technical expertise of teams to successfully drive data transformation initiatives for organisations. He brings a unique combination of strategic thinking and deep knowledge of data and engineering to his consulting work.