Established onxls, all versions: 65,536 rowsxlsx, Excel 2007 and later: 1,048,576 rowsGoogle Sheets: a cell limit rather than a row limit
File formats have limits, and one of them has caused the largest publicly documented data loss attributed to a spreadsheet.
The older binary workbook format holds 65,536 rows in a sheet. The format that replaced it holds 1,048,576. Both numbers are powers of two and both are hard: there is no setting, and no version of the software raises them.
What happened
A public health body in England was collating laboratory results during the pandemic of 2020. The intermediate step used the older format. Each result occupied several columns of a single row, so the effective capacity of a file was a few thousand results rather than sixty-five thousand.
When a file exceeded its capacity, the rows past the limit were not included. Around sixteen thousand positive cases were not passed on for about a week, and the people who had been in contact with them were not traced during that time.
The mechanism, which is the point
Nothing failed. The receiving process opened a file, read every row it contained, and reported success, because from its point of view the file contained exactly what it contained.
The limit is a property of a container. A container that is full does not become an error; it becomes full. Whether anything notices depends entirely on whether somebody compared the number of records sent against the number received, and that comparison was not being made.
How to find out whether a limit is binding
Two checks, both cheap and neither habitual.
Put a count of records in a cell at the top of the sheet, from the data itself rather than typed. Any process that produces a file should record what it believes it wrote, and any process that consumes one should record what it read. Where those two numbers sit beside each other, truncation is visible immediately and permanently.
And look at the last row. If a file arrives with exactly 65,536 rows, or exactly 1,048,576, the number is not a coincidence and the file is almost certainly incomplete. A round power of two at the bottom of a data set is one of the few unambiguous warning signs available in this whole subject.
Why the older format at all
Worth asking rather than assuming carelessness. The older format is what a great deal of laboratory and instrument software emits, because it is what was current when that software was written and because it is read by everything.
Choosing it is a compatibility decision of exactly the kind that appears throughout computing, and the cost of that decision was invisible until the volume rose. That is the general shape: a limit that was comfortable becomes binding when the load changes, and nothing announces the crossing.
The other limits worth knowing
Columns are limited too, at 256 in the older format and 16,384 in the newer one. Text in a cell is limited. The number of characters a formula may contain is limited. And numeric precision is limited to fifteen significant digits, which is not a format limit but an arithmetic one and has its own entry in this part.
None of these produces a message when it binds. Text beyond the limit is truncated on some paths and refused on others; digits beyond the fifteenth become zeros.
What to do instead
Count the records at both ends and compare, which is the general answer to this whole part and costs one cell. Where an export approaches any limit, the answer is not a larger format but a transfer that is not a spreadsheet: a delimited file with no row limit, or a database, which the final part of this site returns to.
What we cannot verify
The row and column limits are documented by the vendor and can be checked directly in any copy of the software. The account of the incident rests on the health body's own statements and on contemporaneous reporting; the precise number of affected cases was revised more than once as the backlog was processed, and figures quoted for it differ between sources.
Where the newer format has limits too
Google Sheets constrains total cells in a workbook rather than rows in a sheet, so the ceiling depends on how many columns are in use and is reached without any single sheet looking full. The limit has been raised several times and is documented by the vendor; check it rather than trusting a figure remembered from a few years ago.
The same shape, one layer up
Row limits are the visible member of a family. Any transfer between two systems has a capacity somewhere: a maximum upload size, a query result cap, an export page length, a report row limit in a reporting tool.
Each of them truncates rather than failing, for the same reason: from the sending side, delivering as much as fits is success. The general remedy is the same in every case, which is to carry the expected count alongside the data and compare it at the other end.
In short
- The older binary format holds 65,536 rows and the newer one 1,048,576.
- Both limits are hard, with no setting and no version that raises them.
- Rows past the limit were not included and nothing reported a failure.
- A full container is not an error; it is full.
- The older format is chosen for compatibility, which is a reasonable decision until volume rises.
- Counting records at both ends costs one cell and is the general answer.