Did Excel make Covid-19 worse?

No, of course not, but a mistake in the use of Excel seems to have affected the numbers being reported.

I’m sure you are aware that nearly 16,000 cases of Covid-19 went unreported until earlier this week and the reports are that Excel is at the centre of the problem.

You might never have thought about it, but Excel has a limit to how many rows of data you can have.  Since 2007 the default has been just over 1 million but before that it was 65,000, and the older version is the culprit in this case.

While Public Health England were receiving data in simple data files which have no limit, they were transferring them into Excel using the old format and thus lost any records above 65,000.

You can tell if your file is the up-to-date format or the obsolete one by looking at the file extension.  The modern version, which you should use all the time, will have .xlsx at the end of the filename.  The old version is .xls.  .xlsx files are better because they are more secure, more stable and take less memory as well as having a much higher capacity (1048576 rows, 16384 columns and as many sheets as you want until your computer runs out of memory. Each cell can hold 32,767 characters).

If you can’t see the file extension I recommend you turn it on.  In Windows File Explorer, click the View tab and tick File name extensions.

Unless you are using a version that is over 13 years old (IE Excel 2003 or earlier), Excel will save your files as .xlsx by default.  In the Save As… dialog box that will be described as Excel Workbook while .xls will be Excel 97-2003.   The only reason why you’d want to save as an .xls is if someone you are working with does not have a modern version of Excel (or Google Sheets).

It beggars belief that people are still working with such an outdated format, but there are a couple of caveats.

In my experience some organisations (and mostly in the public sector) took the decision when upgrading to Excel 2007 to carry-on working with .xls as the default so as to maintain collaboration with organisations that have not updated, and that policy has not been updated in the following 13 years.

Secondly, some modern systems (such as Xero) export to Excel in the .xls format.  I don’t think that happened in this case, but you can be forgiven if you find yourself working in a .xls file.

Finally, this is where I point out that Excel might not be the right tool in this case.  Excel is a number cruncher (it’s best at doing calculations), but for data processing (handling and manipulating lots of data) then a database program (such as Access) is what you should be using.

This problem has come about through ignorance (at best), so if you have any questions or need advice about handling data in Excel or in a database please get in touch and we’ll be able to explain how to do it well in your business.