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.
As a default, sure. Should be one that’s easily changed, though. Repeatedly fighting the machine that’s supposed to do your bidding and make your life easier gets old rather quickly. A machine you own and administrate, let’s not forget that.
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.
Uh, I mean kinda…
Excel implements two Microsoft file format standards:
- ECMA-376
- ISO 29500
Those are not the same and even incompatible in parts. It is correct, that Microsoft tries to use ISO 29500 more, but most files (2007) still are ECMA-376.
But yes, they kinda would have to change their shitty, ISO-incompatible ISO “standard” to fix this issue this way.
Or use the formula field, idk. 😅
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.
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.
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?
Excel sucks open ass. At storing data, at displaying data, at analyzing data. Scientists, of all people, should understand how to use an RDBMS and a data processing framework like R.
I believe that accessibility is what makes Excel so good. And the world agrees.
I’ve seen idiots doing “bioinformatics” with excel & vba. FFS.
What the hell else is there? Good luck getting universities using OpenOffice
Scientists should be using programming languages like R or Python. They are both extremely popular in this field, much more than Excel.
Shoulda coulda. Not everyone is a programmer.
They should be if they’re doing data analysis like this.
If it works, it works. Programming is a tough thing to learn.
Programming in R or Python isn’t a lot harder than learning how to get Excel to do what you want. I’d wager it’s easier since you don’t have to fight your tools.
Excel has its place for simple quick calculations. But at some point it’s simply the wrong tool.
Research projects almost exclusively have more than one person working on them.
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.
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.
Holy shit! Now I just need to talk to some sysadmins and get some group policies set.
Apparently our typical installer for Visio 2016 and our 365 license use “incompatible installers” so it is going to be a pain in the ass for me to have both installed at the same time. Thankfully I’m trusted by IT so I might be able to just do it myself.
Edit: Looks like I’ll need IT after all. https://learn.microsoft.com/en-us/deployoffice/use-the-office-deployment-tool-to-install-volume-licensed-editions-of-visio-2016
MM/DD/YYYY is the correct format here in America.
"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…
Microsoft fixes one of the Excel features that wreck scientific data.
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.
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.
Libre calc is one of the worst UXs I have ever had the displeasure of using. I can’t imagine anyone recommending it is using it as their main work application.
What about text selection knowing better what I want to select?
FFS, scientists should know how to handle data, and Excel ain’t it.
