Biologists rename our own biology to work around Excel "bug"

[NiHaoMike]

https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates

[SiliconWizard]

Good one!

[Infraviolet]

They call themelves "scientists" and yet inist on using M$ excel ! /s

Seriously though, you'd think they'd try working in things like R or Python which give one a more straightforward way to define operations to be performed on inputted data, rather than clicking around in spreadsheet menus and fighting with defaults which cause these sort of bugs. I've known far too many academics who tried to use excel for almost everything.

[SiliconWizard]

They call themelves "scientists" and yet inist on using M$ excel ! /s

Seriously though, you'd think they'd try working in things like R or Python which give one a more straightforward way to define operations to be performed on inputted data, rather than clicking around in spreadsheet menus and fighting with defaults which cause these sort of bugs. I've known far too many academics who tried to use excel for almost everything.

Agreed. (Or Julia, or...)

Then there is Powerpoint too, which is kind of a cancer.
https://www.anti-powerpoint-party.com/

[aeberbach]

They call themelves "scientists" and yet inist on using M$ excel ! /s

The vast majority do.

[xrunner]

Simple to turn off in Open Office -

Click “Format Cells…” Click on the tab “Numbers” Under the category select box click “Text” Click “Ok”. Your text should no longer be interpreted as date/decimal/currency etc for the selected cells.

Just tried it, it leaves these types of inputs as they are:

OCT4
MARCH1

etc ...

[newbrain]

Simple to turn off in Open Office -

Exactly as in MS Excel.
And one can turn off datatype detection when importing CSV.

Sloppy usage of available imperfect tools.

[SiliconWizard]

Well, using Excel (or any basic spreadsheet) for this kind of task would be questionable enough as Infraviolet said, but not even knowing how to change the format of cells and leaving it to default is mind-boggling.
Fortunately, ChatGPT will solve it all.

[Infraviolet]

"not even knowing how to change the format of cells"
And that's part of the reason why the likes of Excel are so unsuitable, you have to know them intimately, or hunt around in multiple menus, to turn unwanted "features" off. Handling these sot of things with actual programming languages ensures you only get the features you ask for where you specifically call them.

[SiliconWizard]

Uh? Intimately? It's just a right click away.

While there are much better tools than spreadsheets for scientific data analysis (and people doing scientific research should master at least one of those), there is absolutely no excuse there. This is nothing intimate. Setting the format of cells is one of the most basic tasks in any spreadsheet even before knowing how to use formulas, even kids know how to do this these days.

[The Soulman]

It's just a right click away.

Have you tried?

[Infraviolet]

"Intimately", I'll admit I dont know much about excel's interface any more, I forgot it all when I learnt to program, especially once I'd found R/RStudio could serve for most of my data handlng needs... But I do remember it was getting ever worse and more confusing every time M$ released a new version with a more graphically stylish (but ever less obvious) interface.

I don't understand why anyone, especially scientsists who surely already know some amount of programming, would put themselves through the torture of Excel for these sort of things.

[Whales]

Without first using excel I would have never had what is needed to understand other languages like R or concepts like arrays.

Excel and spreadsheet software are a standard: they're full of warts, but the compromises mean everyone can play rather than just a select few.  There are many times I wanted to use something more advanced, but I couldn't because I knew the software would not be something my recipients and co-workers could get working. 

[tszaboo]

Simple to turn off in Open Office -

Click “Format Cells…” Click on the tab “Numbers” Under the category select box click “Text” Click “Ok”. Your text should no longer be interpreted as date/decimal/currency etc for the selected cells.

Just tried it, it leaves these types of inputs as they are:

OCT4
MARCH1

etc ...

Not even that much. Just put an ' in front of it. ' tells excel to leave it alone.

[IanB]

Simple to turn off in Open Office -

Exactly as in MS Excel.

According to Microsoft, you cannot turn it off in Excel. This is what I would call a bug, and what Microsoft calls "do what we want, not what you want".

Microsoft Excel is preprogrammed to make it easier to enter dates. For example, 12/2 changes to 2-Dec. This is very frustrating when you enter something that you don't want changed to a date. Unfortunately there is no way to turn this off. But there are ways to get around it.

https://support.microsoft.com/en-us/office/stop-automatically-changing-numbers-to-dates-452bd2db-cc96-47d1-81e4-72cec11c4ed8

[Zero999]

Many scientists used Gnumeric for a long time because it supported larger spreadsheets than Excel.

I'm surprised more scientists haven't dumped Excel. I suppose the problem is it's difficult to transfer some features, such as scripting, but most programming languages can work with data from spreadsheets, even though it's often less convenient.

[coppercone2]

I think its stupid for microsoft not to fix this because it makes big corporations that like hiring the best people for the job (not the people with programming skills) think twice about using their products.

that is a blessing for some crap that started out in accounting made it over to molecular biology gene splicing shit and they are being really neglectful

[IanB]

The appeal of spreadsheets is that they have a visual, parallel UI, which matches the naturally parallel perception of the human brain, and that they provide a no code or low code solution.

Programming languages like R or Python force you to do more work to use them, they force you to write code (even if high level and abstract), and they force your brain to work in a code-oriented manner.

I am more than capable of using programming tools, but I will always go first to a spreadsheet for simple computational tasks.

[Infraviolet]

Personally I've always found programming easier than spreadsheets, ever since I learnt programming that is. Is this unusual? Writing a series of commands and defining variables always feels much more like the algebraic underlying operations than clicking around in a spreadsheet does, and whilst people are (regrettably, as it was with my case) often taught spreadsheets before encountering programming languages, they are taught the basic mechanics of what a programmed series of lines does (the whole concept of maths, and of following recipes) earlier than they encounter spreadsheets. And I struggle to believe anyone "at the coal face"* in biology thesedays wouldn't have been taught a programming language during their degree or early when starting work in the field.

As for "more work" in the likes of R or python, when you're doing one of the common data handling tasks you can always resort to the "copy it from stackoverflow" answr and modify it accordingly to specifics of your individual use case, still better than Excel. And as for collaborating with people who can't program, they can still run a programmed script you give them, and can probably do so with greater confidence than they can try to fiddle in a spreadsheet. Program code in R or Python is all there on show, trying to reverse engineer a spreadshet attempt to handle data is a much more miserable task.

*sure, in biology the older professors might not have learnt programming, but they're also not the ones who'd be handling these demon spreadsheets, they're sitting back having high concept ideas not slogging away at the data directly

[Someone]

as for collaborating with people who can't program, they can still run a programmed script you give them, and can probably do so with greater confidence than they can try to fiddle in a spreadsheet. Program code in R or Python is all there on show, trying to reverse engineer a spreadshet attempt to handle data is a much more miserable task.

How do you propose that end user install the appropriate environment and learn how to execute the code?

The point of excel is that within an organisation it is usually forcibly installed on every computer, so zero friction to share the data/analysis/tool. That then places an expectation on collaborators that they also have that (possibly same version) installed and so the Microsoft virus continues. Open formats were supposed to eliminate that but just try asking for an open format from those people who don't know anything beyond word/excel/power point.

[NiHaoMike]

Many scientists used Gnumeric for a long time because it supported larger spreadsheets than Excel.

When I had to plot data at work far too large for Excel to handle, I used Veusz. Best thing was that I could set up templates for various incoming data formats and have the graphs done very quickly after importing the data.

[Halcyon]

Most scientists and medical researchers don't use Excel. There are far better tools for that, especially when dealing with large datasets.

Even the latest version of Excel has several hard limits, one of which I've encountered when examining log files. Forget more than 1,048,576 rows of information, Excel says no.

[Zero999]

Gnumeric can handle 2²⁴ rows.

[IanB]

Most scientists and medical researchers don't use Excel.

I don't think I could agree with that as a blanket statement. Some don't use Excel, some do. Not all datasets are huge, and sometimes Excel might the the most convenient tool for a job. Almost every university I have seen has their students using Microsoft Office as the standard for reports and other work. When I submit papers for meetings or conferences, the publisher usually provides a Word template to conform to.

[Infraviolet]

Usually academic conferences/journals use a LaTeX template, Word templates are more the thing for business/governmental documents.

Despite being in LaTeX though one so often sees papers where the graphs were clearly done in Excel before being loaded as images. Halcyon is optimistic, researchers shouldn't use Excel but far too many do, and use it for things it really wasn't designed for (the only use of spreadsheets is maintaining lists, as soon as you want to process data you need programming lanugages if you are to do it sensibly).