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.
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. 😅
Excel is never ever going to break backwards compatability. In fact, quite some “features” in Excel are just there to stay bug-for-bug compatible with existing systems.
Example: Excel stores dates internally as a float - called the serial date, you can view it by running DATEVALUE on any cell that contains a date. It is supposed to be the number of days since 1 January 1900. However, since early Excel versions had to be compatible with Lotus1-2-3, Excel had to be compatible with a bug in Lotus123: they had erroneously assumed 1900 to be a leap year. In addition, the indexing is off by one. So the actual 0 epoch of an Excel serial date is 30 December 1899 for all dates starting 1 March 1900.
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.
The idea that any scientist is doing data analysis in Excel is honestly terrifying on every level.
Because every scientist is also a programmer?
Especially if they struggle to use Excel properly, no chance.
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.
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.
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.
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.
