How about just not auto-convert everything and keep the integrity of the data unless specifically asked to? Is that so hard?
Microsoft assumes their users are complete idiots, even when they (the users) are actively trying to convince them (Microsoft) otherwise. No matter how advanced the feature may be, they'll assume you found instructions somewhere to do something entirely unrelated and they constantly have to save you from yourself. As a result you constantly have to fight the OS for access and control to get it to do what you want.
If you're even a bit of a power user that is, of course.
But more often than not Microsoft's assumption is probably spot on.
That assumption is perfectly good for a default. Not a mandatory feature that power users have to live with.
Excel is inherently flawed in its design.
The thing is, that excel already has half the means of what would be necessary to really fix this bug. That is a field for each cell where the original text can stay.
An excel sheet is just a bunch of XML files zipped in a specific structure. You can unpack a file and look for yourself.
Each worksheet is it's own file and each cell is subdivided into the value and the formula, that generated this value (or nothing, if there is no formula).
Excel could easily fix this issue by adding another possible cell attribute like "original" or "plain" that, when set, allows you to roll back any conversion.
But no, they go a half assed way as always and screw up even more.
In order to do that I think they would first have to ratify a standards change to the Excel format, which is open.
Me before reading the article: It's got to be dates. Excel thinks everything is a date.
Me after reading the article: Even the workaround is halfhearted. Jeebus.
Microsoft’s blog adds caveats, such as that Excel avoids the conversion by saving the data as text, which means the data may not work for calculations later. There’s also a known issue where you can’t disable the conversions when running macros.
Apart from actual dates.
The idea that any scientist is doing data analysis in Excel is honestly terrifying on every level.
You don't want to know...
I remember when a biologist asked us for help - Excel crashed on processing his 700MB tables. Took some time and Chatgpt to convince him to do the analysis in R. It worked out in the end and he is now recommending this solution to his colleagues, which is nice.
Flashback to the time the UK government lost 16,000 positive COVID patients because Excel has a 1 million row limit.
If only there were better ways of storing large amounts of records with a fixed structure. Maybe the future will provide such technology...
And is so bad at it that they can't work around this issue.
Excel is excellent at data analysis... Python integrations and everything
As an alternative, maybe just Python?
Because every scientist is also a programmer?
Especially if they struggle to use Excel properly, no chance.
Thank god! You have no idea how awful this is for scientists. Need to paste some gene names down? Better hope it’s not MARCHF8 or in the Septin gene family, otherwise you have to convert columns to text then import the data. Seems like a simple fix, but many wet lab biologists are technologically challenged.
"Microsoft’s blog adds caveats, such as that Excel avoids the conversion by saving the data as text, which means the data may not work for calculations later. There’s also a known issue where you can’t disable the conversions when running macros. "
This sounds very half assed...
It's no good having this as part of the user options. It should be a sheet characteristic and the default should be "keep cells exactly as entered regardless of data type".
Changing the default will break the workflows of tens of thousands in the business industry
Scientists should be using something like MATLAB, not Excel.
Matlab is used, if at all, by physicists.
We're talking about molecular biologists.
Now if only it would stop dropping leading zeros unless you ask it, and we got rid of the MM/DD/yyyy date format entirely.
Now if only it would stop dropping leading zeros unless you ask it
That appears to actually be a feature.
This is the best summary I could come up with:
In 2020, scientists decided just to rework the alphanumeric symbols they used to represent genes rather than try to deal with an Excel feature that was interpreting their names as dates and (un)helpfully reformatting them automatically.
Yesterday, a member of the Excel team posted that the company is rolling out an update on Windows and macOS to fix that.
Excel’s automatic conversions are intended to make it easier and faster to input certain types of commonly entered data — numbers and dates, for instance.
But for scientists using quick shorthand to make things legible, it could ruin published, peer-reviewed data, as a 2016 study found.
Microsoft detailed the update in a blog post this week, adding a checkbox labeled “Convert continuous letters and numbers to a date.” You can probably guess what that toggles.
The update builds on the Automatic Data Conversions settings the company added last year, which included the option for Excel to warn you when it’s about to get extra helpful and let you load your file without automatic conversion so you can ensure nothing will be screwed up by it.
The original article contains 225 words, the summary contains 184 words. Saved 18%. I'm a bot and I'm open source!
20 years after the problem was first reported.
Meaning there's still hope for XDG support in Firefox?
Microsoft fixes one of the Excel features that wreck scientific data.
This isn't a fix. Excel wasn't meant for this. While I do understand it's convenient as a database, unless you're doing something unimportant and small you just really should use something proper. And even now that this "problem" is gone, I am certain there are still more things that cause trouble. You can not satisfy everyone and Excel was just... not made for gene info storage.
Even if you don't want to use stuff that isn't Microsoft Office, that comes with Microsoft Access, which is a proper database management system. It's literally in the same software package, so why do people refuse to use it?
Why would you need a full blown (shitty) relational database management system to store gene info? Excel should be just fine for storing data in arbitrary tables. It shouldn't make assumptions about your data by default, and changing values that look like they're in a specific format should be opt-in, not default behavior.
It shouldn't make assumptions about your data by default, and changing values that look like they're in a specific format should be opt-in, not default behavior
But that's exactly what made the "auto" data type of Excel such a powerful tool when introduced. If you're storing text, make the datatype "text", problem solved.
Nowadays, when making stuff like Excel from scratch, you could opt for a "these look like dates, change the type from 'none' to 'date'?" but with middle management being conditioned on the data type being 'auto', that's something that's hard to change.
Optimist: The glass is half full.
Pessimist: The glass is half empty.
Realist: The glass is twice as big as necessary.
Excel: The glass is the 2nd of January.
Convinient arbitrary table software goes brrrr.
Do you really don't know why, or are you being sarcastic?
It's too late though, scientists already had to rename the genes. Although of course there are other things that can trigger it, not just in science.
From the article:
The problem of Excel software (Microsoft Corp., Redmond, WA, USA) inadvertently converting gene symbols to dates and floating-point numbers was originally described in 2004 [1]. For example, gene symbols such as SEPT2 (Septin 2) and MARCH1 [Membrane-Associated Ring Finger (C3HC4) 1, E3 Ubiquitin Protein Ligase] are converted by default to ‘2-Sep’ and ‘1-Mar’, respectively. Furthermore, RIKEN identifiers were described to be automatically converted to floating point numbers (i.e. from accession ‘2310009E13’ to ‘2.31E+13’). Since that report, we have uncovered further instances where gene symbols were converted to dates in supplementary data of recently published papers (e.g. ‘SEPT2’ converted to ‘2006/09/02’). This suggests that gene name errors continue to be a problem in supplementary files accompanying articles.
What about text selection knowing better what I want to select?
Office Libre is free, and modern MS Office UIs looks like dog dookie. OL can also save in Excel format if you want.
Hey look at that, I found a solution that didn't require they change their entire process or have to wait for Microsloughed to get their act together.
This is a most excellent place for technology news and articles.
