ICAEW.com works better with JavaScript enabled.

COVID data loss flags fatal flaws in spreadsheet knowledge sharing

Author: Simon Hurst

Published: 01 Jul 2021

The ongoing publicity around the Excel issue that led to the loss of contact tracing data underscores the need for organisations such as Public Health England to share information on spreadsheet errors for the good of all, writes Excel lecturer and chartered accountant Simon Hurst.

Anyone keen to forget a working week spent staring at Excel's familiar grid of cells might have had an unpleasant shock at the end of June, when the FT Weekend magazine fell out from the weekend papers next to their cup of coffee. Emblazoned on the front cover was a spreadsheet grid with 'NASTY LITTLE BOXES' in very large letters in the foreground. On the inside pages, the same grid carried the title of the article: 'TYRANNY OF THE SPREADSHEET'.

In fact, the lurid headlines didn't really reflect the more considered content of the article itself. The author, Tim Harford – a Senior Columnist at The Financial Times – took as the starting point the October 2020 Coronavirus contact tracing error that had temporarily 'lost' nearly 16,000 cases as part of the transfer of data from Public Health England (PHE) to the NHS contact tracing service. 

Towards the end of the article, Harford cited a study by the economists Thiemo Fetzer and Thomas Graeber that calculated the error could have resulted in 125,000 additional infections and at least 1,500 additional deaths. Harford described those who died as 'unknown victims of the spreadsheet error'.

Many errors are labelled as spreadsheet errors, often correctly so. However, sometimes spreadsheets appear to be a convenient scapegoat rather than the actual cause of the error. As argued in a Tech News article coincidentally published a few weeks before the contact tracing issue, a distinction should be drawn between errors that occurred because a spreadsheet was part of the process and errors that would have occurred whatever method or application had been used.

I'm certainly not accusing Harford of 'lazy journalism'. He has gone to great lengths to try and discover the full story of what happened and one of the few things that we seem to know for sure was that the error resulted, in part at least, from the use of a particular Excel file format. In an article for BBC News, Leo Kelion described what he had discovered about the key elements of the process for transferring the lists of cases and stated that: "The problem is that PHE's own developers picked an old file format to do this - known as XLS."

Many of us have tried to work out exactly what happened. Harford has contacted PHE directly and canvassed the opinion of several members of the European Spreadsheet Risk Interest Group (EuSpRIG). Although we probably all have our own ideas, without more information from those actually involved, we can't know the full story. 

This is a crucial issue. PHE has a duty to make the full details public. Very few spreadsheet errors are original, most have happened before and will go on to happen again. Pretty much all of us make mistakes - the key is that we learn from them and, wherever possible, ensure they are not made again and help others to avoid them. 

This is particularly true where public organisations are involved and where the stakes are as high as they were in this case. Someone does know what caused this problem, and any supposed issues of commercial confidentiality or personal accountability should be set aside so that the right people can learn the right lessons.

It might be that, in providing an adequate explanation, we all learn about some deep-seated technical spreadsheet deficiency that we can avoid or mitigate. I think it more likely that we would learn that there was no particular 'spreadsheet' issue but instead a failure of management to implement proper checks and controls. Should this be the case, proper disclosure of the circumstances in such a high-profile case could achieve a great deal in increasing the awareness of the importance of treating spreadsheets and other similar applications seriously.

Running through much of the commentary on this, and other, spreadsheet failures is the conviction that, wonderful as spreadsheets can be, using them inappropriately can be disastrous. This is usually seen in terms of the potential for catastrophic error, but insidious inefficiency is probably a far bigger issue financially. All spreadsheet users owe it to their colleagues, their employers and their own career progression to achieve a level of spreadsheet skill and knowledge appropriate to the work they do. A key part of this is the ability to identify when a spreadsheet is not the right tool to use for the job.

Professional bodies, whether legal, financial, engineering or other, have a special responsibility to ensure that their members have appropriate spreadsheet skills. This is perhaps particularly true of the accounting bodies. Not only can they influence their own members, but their members can in turn influence their colleagues and clients, potentially reaching hundreds of thousands of businesses and individuals. 

ICAEW has embraced this responsibility in recent years with some significant spreadsheet projects. The publications associated with these projects are all publicly available: Twenty Principles for Good Spreadsheet Practice, the Spreadsheet Competency Framework and the Financial Modelling Code. This is not the end of such projects: ICAEW is currently asking for comments on the exposure draft of their latest publication: How to review a spreadsheet

Most of us might be lucky that our spreadsheet errors could only cost us, or our organisations, large sums of money, or just destroy our professional reputation, rather than being a direct threat to life. Together with Harford's subsequent research and conclusions, this case demonstrates that, however much we might love or hate spreadsheets, we can't afford to underestimate the vital contributions that they make in so many organisations – to both good and bad outcomes. Featuring spreadsheets on the cover of FT Weekend Magazine is a significant step forward in publicising the need for us all to take the issue of spreadsheet misuse seriously.