Why Did the UK Lose 16,000 COVID Cases? The Excel Row Limit Explained

In October 2020, it emerged that Public Health England had failed to report nearly 16,000 confirmed COVID-19 cases, delaying contact tracing at a critical point in the pandemic. The cause turned out to be a spreadsheet.

Contents:

1. Quick Answer

2. What Actually Happened

3. Why Does the Row Limit Exist?

4. How to Avoid This Problem

Quick Answer

Public Health England lost 15,841 COVID-19 cases between 25 September and 2 October 2020 because the Excel files used to collect positive test results were saved in the old .xls format, which hits a hard ceiling at 65,536 rows. When the files became full, new test results were silently dropped rather than flagged as an error. Those missing cases were never passed to contact tracers in time.

What Actually Happened

The lab results pipeline worked like this: testing labs uploaded their positive results as CSV files, those files were then imported into a master Excel workbook, and that workbook was sent on to contact tracers.

The problem was with the file format. The files were saved as .xls (the older Excel 97-2003 format) rather than the modern .xlsx format. The .xls format has a hard row limit of just 65,536 rows. Once that ceiling was hit, any new rows added to the bottom of the file were silently cut off.

Nobody received an error message. The file just quietly stopped accepting new records. Over the course of about a week, 15,841 positive cases accumulated in the overflow and were never forwarded to contact tracers.

By the time the gap was discovered and the missing cases were reported, most of the close contacts of those positive individuals were beyond the window where isolation would have been effective.

Why Does the Row Limit Exist?

Every Excel file format has a fixed maximum number of rows it can hold, because the underlying binary structure pre-allocates a fixed address space for cell references.

The modern .xlsx format, introduced with Excel 2007, raised the row limit to 1,048,576 (which is exactly 2 to the power of 20). That is a big improvement over .xls, but it is still a hard ceiling. A busy enough data pipeline can hit it too.

For true large-scale data, tools like SQL databases, Power Query, or Python have no practical row cap. If you regularly work with data that might grow unpredictably large, it is worth knowing about those options before you hit a wall.

How to Avoid This Problem

A few simple habits would have prevented the PHE incident entirely.

First, use .xlsx instead of .xls. There is almost no reason to save files in the old .xls format today. If you are ever unsure which format a file is in, check the extension in the title bar or file explorer.

Second, never use Excel as a live data collection pipeline for unbounded data. Excel is great for analysis, reporting, and modelling. It is not a database. When results are flowing in continuously from an external system, those records should land in a proper database first, then be pulled into Excel for reporting.

Third, if you must use Excel for a pipeline like this, build in a row-count check. A simple formula like the one below, placed somewhere visible, can alert you before the file fills up.

= ROWS(A:A) - COUNTA(A:A)

This returns the number of empty rows remaining in column A. You could pair it with conditional formatting to turn the cell red when the remaining capacity drops below a threshold.

The PHE case is a good reminder that Excel is a tool with real limits. Understanding those limits, like knowing how sheets and rows are bounded, is part of using it responsibly.