First I thought this story was about math and numerical errors. But it's actually about auto- formatting and auto-correction.
"Excel automatically converting gene names to things like calendar dates or random numbers"
In this case, I think what is needed is some kind of rudimentary knowledge of data-types. Or perhaps more simply a scientific template which is actually plain text by default.
But how are people not noticing auto-correction and auto formatting taking place!
The only perfect solution is to hire a developer to build you a data entry system. The developer can build the system which they have no cause to entirely understand the science behind, and thus a human to take the blame for errors instead of excel.
The worst with these formatting is that Excel will behave differently in different regions. Another headache in large international corporations/collaborations.
But that's an advantage too. As a non English-native user, I like when companies care about my language and culture and they do not stick dates to the MM-DD-YYYY format that is used by Americans only. Yes, of course, we should all go for YYYY-MM-DD... fine by me, but now you go telling my mom :¬)
I've found it hard to get Excel to turn off the coercions. In telecom tons of inter-company data is exchanged in Excel or CSV files. And very often numbers show up in scientific notation. Or timestamps (10.2 minutes) get coerced into dates. It's infuriating. Sometimes the manual formatting to no format works and sometimes it doesn't. I feel dumb admitting that I can't seem to figure something so basic out.
Excel should really offer an easy way around this.
My experience is the same, also in telecom/networking. One of our vendors' serial numbers, when entered into Excel, get converted to something else entirely (not sure what exactly), and setting the fields to "text" or "numbers" doesn't seem to do much, consistently.
How people don't notice: you have a list of thousands of gene names. It's impossible to be familiar with all of them, so it's hard to notice if one of them gets auto-corrected to something else.
"Excel automatically converting gene names to things like calendar dates or random numbers"
In this case, I think what is needed is some kind of rudimentary knowledge of data-types. Or perhaps more simply a scientific template which is actually plain text by default.
But how are people not noticing auto-correction and auto formatting taking place!
The only perfect solution is to hire a developer to build you a data entry system. The developer can build the system which they have no cause to entirely understand the science behind, and thus a human to take the blame for errors instead of excel.