Excel’s pervasiveness has resulted in it being used (correctly or incorrectly) in just about every area of science.
Unfortunately, Excel has some traps for the new player and unless you’ve fallen for them before, they are not entirely obvious. They stem from the fact that Excel will try to help the user by reformatting data into what it thinks you mean.
When attempting to herd genetic data in Excel, “Membrane Associated Ring-CH-Type Finger 1” (MARCH1) got eaten by Excel and reformatted as a date, i.e. 1-March (the first of March).
“Septin 2” — SEPT2 suffered a similar fate and ended up as 2-September and likewise RIKEN IDs such as ‘2310009E13’ are automatically converted to floating point numbers (i.e. from accession to ‘2.31E+13’)
By starting with a new spreadsheet and selecting Data > Get Data > From File it’s possible to override this automatic formatting, however should you send your processed data to someone, it’s entirely likely they’ll just double-click on it to open it and the formatting issues reappear. This is especially tiresome in large spreadsheets where opening the data may result in it initially looking correct, however an auto-formatting error may be lurking several hundred rows in and is not revealed by a cursory glance at the data.
This problem is so prevalent that a 2016 study found that of supplementary data published alongside 3,597 papers, approximately 20% had Excel errors.
As these issues have leaked into so many pieces of work, Guidelines for human gene nomenclature have been released. Whilst undoubtedly helpful, alas these advise against entertaining names so we may not be seeing any more Sonic Hedgehog or Indy (I’m not Dead Yet) genes.
Don’t be complacent if you’re not dealing with genes. For example, University of Oxford Bod cards have 14-digit hexadecimal numbers embedded in them, of which 4 numbers are decimal. There’s a 0.9% chance someone’s spreadsheet is going to think you’re a floating point. A 0.9% chance doesn’t seem that high at first glance but “Million-to-one chances crop up nine times out of ten.” ― Terry Pratchett, Equal Rites
Excel has also been blamed for Public Health England misplacing nearly 16,000 Covid test results. Which is still slightly disturbing given that modern versions of Excel have a limit of 1,048,576 rows by 16,384 columns.
I think the take home message for this might be for us to try to free ourselves from the ideas:
- We all use the same tools for this – No, we don’t.
- Everyone knows how to parse this format.. – Bah, who wants to write their own parser? There’s a standard library to parse this. “The good thing about standards is that there are so many to choose from.” ― Andrew S. Tanenbaum
- And finally perhaps consider : “What’s the most basic tool someone would use to
manglehandle my data?”
