Recent news articles are showing that even COVID is not immune to spreadsheet errors. As per this Wired article about Spreadsheet Errors, a lab spreadsheet undercounted cases as it reached its maximum size!
Given that there is no theoretical maximum with file size, we assume that it is one of the limits on columns or rows. Excel’s column limit is 16 384 and the rows limit is 1 048 576 rows. As a result of this COVID spreadsheet error, thousands of cases have been missed.
Update- wrong Excel file format used
More information has come to light. It seems that an upload system could only use XLS files as opposed to the new XLSX version. Seems minor but an XLS file has a maximum of 256 columns and 65 536 rows per sheet and a XLSX file is the aforementioned 16 384 columns by 1 048 576 rows.
Avoiding Spreadsheet Errors
This emphasises that the planning in the use of spreadsheets is important (if a spreadsheet should be used at all for this type of information)! Understanding not only what you need now, but anticipating what might happen if there is unexpected changes.
It is important to build in as many error checks as possible. Even a simple error check in column 16 384 saying ‘If this column is used, warn me’ would be acceptable!
To avoid MS Excel spreadsheet errors have a look at these 2 free online courses.
- Spreadsheet error examples (and how to avoid them)
- MS Excel best practices for error free spreadsheets
If you need help in finding spreadsheet errors you must look at Excel Auditing tools. Ask us about the Excel Auditing tool we provide