UK Learns Excel has a row limit in the hard way by missing 16K COVID cases
The extraordinary meltdown was caused by an Excel spreadsheet containing lab results reaching its maximum size, and failing to update. Some 15,841 cases between September 25 and October 2 were not uploaded to the government dashboard. As well as underestimating the scale of the outbreak in the UK, critically the details were not passed to contact tracers, meaning people exposed to the virus were not tracked down.
A row limit on Microsoft’s Excel spreadsheet software may have led to Public Health England misplacing nearly 16,000 COVID test results, it is understood.
The extraordinary meltdown was caused by an Excel spreadsheet containing lab results reaching its maximum size, and failing to update. Some 15,841 cases between September 25 and October 2 were not uploaded to the UK government dashboard.
As well as underestimating the scale of the outbreak in the UK, critically the details were not passed to contact tracers, meaning people exposed to the virus were not tracked down.
The BBC has confirmed the missing Covid-19 test data was caused by the ill-thought-out use of Microsoft's Excel software. Furthermore, Public Health England (PHE) was to blame, rather than a third-party contractor.
The issue was caused by the way the agency brought together logs produced by the commercial firms paid to carry out swab tests for the virus.
They filed their results in the form of text-based lists, without issue. PHE had set up an automatic process to pull this data together into Excel templates so that it could then be uploaded to a central system and made available to the NHS Test and Trace team as well as other government computer dashboards.
The problem is that the PHE developers picked an old file format to do this - known as XLS. As a consequence, each template could handle only about 65,000 rows of data rather than the one million-plus rows that Excel is actually capable of.
The problems are believed to have arisen when labs sent in their results using CSV files, which have no limits on size. But PHE then imported the results into Excel. Since each test result created several rows of data, in practice it meant that each template was limited to about 1,400 cases. When that total was reached, further cases were simply left off.
However, the technical issue has now been resolved by splitting the Excel files into batches.
How the rate per 100,000 looked across England in the week to 1 October on Friday, before the glitch was found, and how it looked last night.
Excel is a very dangerous tool to handle big data in the first place. In 2013, an Excel error at JPMorgan masked the loss of almost $6bn (£4.6bn), after a cell mistakenly divided by the sum of two interest rates, rather than the average. The news led James Kwak, a professor of law at the University of Connecticut, to warn that Excel is “incredibly fragile”.
Errors from the spreadsheet software have even changed the very foundations of human genetics. The names of 27 genes have been changed over the past year by the Human Gene Nomenclature Committee, after Microsoft’s program continually misformatted them. The genes SEPT1 and MARCH1, for instance, have been changed to SEPTIN1 and MARCHF1 after they were repeatedly turned into dates, while symbols that were common words have been altered so that grammar tools didn’t autocorrect them: WARS is now WARS1, for instance.
This is not Excel's fault, any more than it would be the fault of a Swiss army knife if you used it to build a bridge and the bridge fell down. It's simply the wrong tool for the job, and the fault is with whoever decided to use it in critical systems.