Scientists rename human genes to stop MS Excel from misreading them as dates

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

by virde • 6 years ago

621 506 6 years ago

Or perhaps stop using Excel?

chapium• 6 years ago

Excel is pretty efficient at ad-hoc text processing. You get real time feedback as you change rules and there are lots of handy functions to help you along. Its like a tabbed portable jupyter notebook.

kaonwarb• 6 years ago

Love this analogy. Yes, Excel is error-prone at its core. It's also the most widely-used and accessible IDE yet created. Those statements are correlated.

DethNinja• 6 years ago

What’s the alternative?

hutzlibu• 6 years ago

Well, everybody should set up their own, custom script powered favourite DB of course.

Ok, seriously, it would be nice to te able to recommend a alternative. I heard LibreOffice Calc is not really equivalent?

proactivesvcs4• 6 years ago

And what are the alternative's silent pitfalls, areas of common misunderstanding et al? Replacing a tool because it is not used properly is just asking to receive another tool to be misused.

dade_• 6 years ago

I use LibreOffice Calc for most of my work. It works just fine.

The question I have is not about using spreadsheets, but why Excel? It takes way too long to load and the UI gets worse with every iteration. Most people don’t even use 99% of the features in Excel (Find and Replace amazes some people). It is headed on the same trajectory as the abomination of diagramming software known as Visio.

treeman79• 6 years ago

Excel is amazing at what it does.

I had a Manager that spent an entire week compiling a report on how much times people spent on tasks.

He was very proud as he was showing me this report. I asked him why he didn’t use a pivot table.

While he was asking what that was, I spent two minutes on the same data that he had and reproduced his report. I had a friend and patron for life after that. I was involved in many major decisions for a fortune 50. All because I could work excel.

sam_lowry_• 6 years ago

These Excel people feel like they know something while they are just nefarious bugs ;-(

mobilio• 6 years ago

This happens before, and will happen again: https://qz.com/119578/damn-you-excel-spreadsheets-jp-morgan-...

lvturner• 6 years ago

I think another question is that why isn't there a viable alternative to excel for this particular use case?

Is there, perhaps, a market for niche spreadsheet applications that serve one particular market?

hairofadog3• 6 years ago

I’ve been wishing for something like this lately in the apple ecosystem: sort of a “plain text” tabular editor that can handle large data sets. Numbers feels like it’s designed for presentations more than data crunching, and Excel (or at least the price of excel) would be overkill for my needs (which largely consists of data cleanup, spot-checking, and quick calculations). I’d happily pay for an IA-Writerish spreadsheet app.

Edit: or maybe Sublime Text is more what I mean. In any case I’m hoping someone will pop in and say, “well why aren’t you Snappets!?” and then I’ll go buy a copy of Snappets.

Legogris• 6 years ago

Not exactly what you're asking for, but what about libreoffice?

bransonf• 6 years ago

See this Exchange thread [0]

I personally use Table Tool for the most basic editing/collection.

[0] https://apple.stackexchange.com/questions/15946/free-csv-tex...

brisance• 6 years ago

Have you taken a look at Wizard?

https://www.wizardmac.com/support.html

hairofadog3• 6 years ago

Thanks, I'll check these all out!

takluyver5• 6 years ago

Educated guesswork: this isn't one particular use case, but a whole bunch of use cases with one thing in common: gene names are put in a table. Excel is the familiar, ubiquitous tool for tabular data.

jpindar6• 6 years ago

I'm sure there is, which is great for one's own use but you can't make everyone else use it.

yummypaint• 6 years ago

Why in the world is excel the application of choice? Last time I tried to use it (for much less complicated things than genetics), it choked and became unusable when the filesize exceeded about 6MB. I have yet to encounter a spreadsheet oriented task that isn't better implemented in gnumeric. Maybe we should also shorten all publications so adobe reader can display them without crashing?

zapdrive• 6 years ago

Maybe you should ditch your Pentium 1 and buy a newer computer?

mellow2020• 6 years ago

If the same machine can do it without hanging with better coded software, the hardware obviously isn't the problem.

NikolaeVarius• 6 years ago

Excel is the application of choice because the world runs on Excel. Get with the times.

curiousllama2• 6 years ago

I work in excel every day and, if this is true, you needed to spend like 30 seconds on google to find a half dozen solutions to this. Turn off auto-calculations, delete pivots, write fewer vlookups, etc...

It's roughly the equivalent of saying "whenever my C program gets too big the 'core dumps,' whatever that means - a totally useless language"

airstrike5• 6 years ago

I regularly use 10MB+ files with circular calculations turned on and it's no biggie most of the time

catalogia3• 6 years ago

Why in the world is excel the application of choice?

Because if it weren't for Excel, most Excel users would have to hire programmers. (And Gnumeric is very obscure, how many non-programmers have heard of it?)

numpad02• 6 years ago

Excel’s problem is it destroys the original keystrokes.

Anyone know why? It makes little sense to me.

dkarl• 6 years ago

Because usability is measured by the naive expectations of the least sophisticated users.

numpad02• 6 years ago

But haven’t you seen the least sophisticated users trying to type in the same sequence until it sticks?

proactivesvcs4• 6 years ago

In this case, because it's much faster to enter data such as dates by entering the shorthand - e.g. 6/8 - than have to type 06/08/2020. The same reason it's very helpful to type "500" into a currency cell and have it convert to "£500.00", or the many other sorts of autotype.

airstrike5• 6 years ago

Because most of the time you actually want it to be a date and those are stored in the spreadsheet as integers

You can add a single quote before any value to prevent Excel from autoformatting

Or just turn off autoformatting entirely

jordigh5• 6 years ago

To people asking, "why do they use Excel?" that's like asking "why must we be subjected to gravity?"

The whole world's data ultimately comes from or ends up in an Excel spreadsheet. Sure, we might use other intermediate data storage methods, but in the end it's going to go into some scientist's or some politician's computer, and by golly it's gonna be in Excel. Trying to rally against Excel is like trying to rally against fundamental forces of nature.

This is just an example of that fundamental force winning.

pratio2• 6 years ago

True in every sense. We shifted from google sheets to O365, there's just no way to go around excel. Its efficient and every one from a novice to expert can find their way around it.

pmarreck3• 6 years ago

Pray tell, why did you switch from Sheets?

azalemeth• 6 years ago

" To people calling, "why do they use Excel?" that's like asking "why must we be subjected to gravity?""

I respectfully disagree with this. Excel is fundamentally not suited to analysing *omics data. It's often the default program affiliated with a .csv filetype on people's computers, but trying to get an entire field of scientific research to rewrite itself based on its glorified bugs is...wrong, in my opinion.

If you see wrong things in the world, do you accept them as they are, or try -- however ineffectually -- to force change for the better? I for one bang the drum into the wind and try to get biochemists off it. I teach people to be very sceptical of excel in my stats courses, for example (aside from some showstopping bugs and downright dangerous defaults, its RNG is particularly crap).

jordigh5• 6 years ago

Excel is not fundamentally suited, but it's fundamentally used.

I wish you luck, but I long ago declared defeat against Excel. I can't prevent everyone from using it. I have come to accept that whatever Excel does is what I have to live with, because someone, somewhere out there hasn't heard the anti-Excel gospel yet.

appleflaxen• 6 years ago

Excel is fundamentally not suited to analysing *omics data

you are 100% correct. and yet it will continue to happen.

ChrisLomont3• 6 years ago

based on its glorified bugs

If someone types MARCH1, DEC1, OCT4, SEPT1 into a column, why should Excel not treat these as dates? Probably 99.999% of users would prefer this auto-understanding. To make users have to then select cell formatting and select date is a terrible UI decision.

It's odd to call such a useful feature a bug.

Excel is fundamentally not suited to analysing *omics data

But it's excellent for a massive range of basic analysis and especially for a unified platform to share results, which is why everyone uses it. It's good for publication or proposal steps of creating graphs from data output from other more specialized systems, that usually lack the flexibility and widespread use that Excel has.

If my group uses some specialized, often in house, analysis software, my results cannot be spread to others unless I put them into a more common and accessible format. And nothing in the genomics and wider scientific community has anywhere near the widespread availability as Excel.

You might as well complain they also use word docs instead of Latex or troff. Not everyone wants to spend days fighting software instead of doing work.

dr_orpheus2• 6 years ago

Having Excel be the default for .csv files has caused me problems in the past as well with Excel automatically changing data when you open it. The unfortunate thing I have come across multiple times is a csv file that has some values with a lot of significant figures, i.e.

123456789876, 987654321234

After opening in Excel it will convert these to:

1.23E+11, 9.87E+11

Then resaving the file (as a csv) will store the values in scientific notation and lose all of the digits.

tgb• 6 years ago

Surely then the blame should go one step further back: .csv and tab-separated are poor file formats for *omics data and excel (and pandas, etc.) have no choice but to guess data types and metadata. Unfortunately I'm just not sure there's any format that's sufficiently better to ever replace them.

vikramkr6• 6 years ago

But we didn't accept them as they are. We looked at two options, change excel or change gene names, and picked the one most likely to solve the problem. Gene names mean nothing anyway. We're going from random letters that mean nothing to humans and computers to still random letters that mean nothing but at least don't get confused for dates.

meow10324• 6 years ago

I don't disagree completely with this, but just want to point out that it's kind of a bad smell to have computational biologists who are - as someone in the article puts it - computationally illiterate. I have met lots of these types over the years, and usually their methods are kind of a gong show. If you can't properly sanitize your data inputs on your column headers, why should I trust that you've treated the rest of your data properly?

kang• 6 years ago

https://news.ycombinator.com/item?id=24070913

acidburnNSA• 6 years ago

I have a strong feeling that, if people really put an effort into reading and replicating more papers, we would find that a lot of what's being published is simply meaningless.

In grad school I had a subletting roommate for a while who was writing code to match some experimental data with a model. He showed me his model. It was quite literally making random combinations of various trigonometric functions, absolute value, logarithms, polynomials, exponents, etc. into equations that were like a whole page long and just wiggling them around. He was convinced that he was on a path to a revolution in understanding the functional form of his (biological) data, and I believe his research PI was onboard.

I guess "overfitted" never made it into the curriculum.

epistasis7• 6 years ago

It's not usually computational biologists who are using Excel.

What if you want to share data with a wetlab biologist who want to explore their favorite list of genes on their own?

gameswithgo2• 6 years ago

it would be nice if everyone was expert at everything, but they cant be. it would be nice if they hired experts but money doesn’t grow on trees. we often insist on a degree of excellence we refuse to pay for

Balgair3• 6 years ago

In grad school I had a friend that was doing olfactory (smell) research on rats with tetrode drives (wires in their brain). He was looking at the neuronal response to smells that they gave the rats and had a few signals to match up. There was the signal from the arduino running the scent gates, the amps that reported the weak neuronal currents, the nose lasers that gated the ardunio, etc. He was having a hard time getting through all the data in his MatLab code and I offered to help for some beer.

After the 11th nested 'if' statement, I upped the request to a case of beer. I'm not certain he ever got the code working.

To the larger point, scientists are not programmers. They got into their programs to do research. What keeps them going is not the joy of programming, but the thrill of discovery. Programming is nothing but a means to an end. One they will do the bare minimum to get working. Asking hyper stressed out grad students to also become expert coders isn't reasonable.

And yes, that means that the code is suspect at best. If you load the code on to another computer, make sure you can defenestrate that computer with ease, do not use your home device.

t34saves• 6 years ago

Considering how Perl was chosen as the computational biologists lingua franca in 1990's - 2k's since it was good at text manipulation (since genes are represented by text) I would say they don't have a history of making good choices.

dekhn3• 6 years ago

I'm an ex-computation biologist who did most of his work in python but periodically had to interop with excel.

THe basic assumption I have is that when I input data into a system, it will not translate things, expecially according to ad-hoc rules from another domain, unless I explicitly ask it to do so.

It's not clear what data input sanitization would mean in this case; date support like this in Excel is deeply embedded in the product and nobody reads the documentation of Excel to learn how it works.

sincerely• 6 years ago

Sorry for asking but are you familiar with how MS Excel aggressively converts data to dates? There's no way to "sanitize" it (without resorting to hacky solutions like including extra characters) and even if you fix the data, it will re-change them to dates the next time you open the file.

jhbadger• 6 years ago

For the most part we aren't talking about computational biologists but experimentalists using Excel. People at the bench need to collect their data somehow, and using Excel for tabular data and Word for text data is just what they know. Typically they then pass these files over to computational biologists for analysis. Yes, it would be nice if they would use more appropriate tools, but I know from experience that the typical result of trying to teach them better tools is the experimentalists just rolling their eyes and saying that they don't have time to learn some nerdy program because they have experiments to run.

Myrmornis• 6 years ago

It's also my experience of research in biological sciences that it is a widespread belief/fact that in order to get published in a top journal, the analysis methods must be "fancy", for example involving sophisticated statistical techniques. I worked on computational statistical methods so I'm not against that per se, but the problem is that if you have the training to contribute at the research front of an area of biology you rarely have the training to understand the statistics. Some would say that the collaborative publication model is the solution to that, but in many cases the end result isn't what one would hope for. I do think that less emphasis on "fancy" statistics, and more emphasis on simple data visualizations and common sense analyses would be a good thing.

fortran77• 6 years ago

Excel is a wonderful tool and a type of programming that is very accessible to many people. I use it all the time.

agumonkey• 6 years ago

And other fields are probably stuck with bad word processing papercuts.

wittyreference• 6 years ago

This accords with my experience.

When I worked in clinical trial design/analysis, no one did their analyses in excel, but for initial data formatting and clean-up? /everything/ went through excel, even if the final cleanup was a python script.

You’re not going to have the same ease of eyeballing your data in SPSS or SAS or R.

ekianjo2• 6 years ago

I'd wage for formatting and clean-up, it's a lot faster to proceed via R or Python (notebooks), and it can be reproduced a lot more easily.

t-c-h3• 6 years ago

I understand that Excel allows for a quick glance, but I'm not sure how R does not offer the same visual cues? Assuming that the data originates from some csv/table structure, Excel requires some GUI clicking and R needs a read.csv() call.

I would say R is a lot more useful for a quick check on whether all columns have one data structure, contain NaN/NA's, are of equal length, etc.

donatj• 6 years ago

So much this. I work in educational software, and have a little write up about the troubles we’ve had with schools “fixing up” CSVs from their student information systems before sending them to us. The simple act of opening the CSV and hitting save loses data (Microsoft actually fixed some of the problem very recently). That said, they will use Excel, it’s not in my control.

I received dozens of comments “Just use Open Office / Libre Office”. I am but a man, I cannot change the world. I forgive that which I cannot control. We work with tens of thousands of schools. Getting all of their administrators to install Open Office, and thousands of administrative assistants to remember to use it rather than Excel is simply an impossibility.

- https://donatstudios.com/CSV-An-Encoding-Nightmare

JoBrad• 6 years ago

I’ve shifted to trying to get people to use the PowerBI data import function, at least. It offers the ability to import your data without managing it.

BurningFrog• 6 years ago

If the gravity metaphor doesn't reach you, maybe this will:

The world speaks English. It's an illogical and absurdly spelled language. Esperanto is miles ahead on all logical measures. Yet we will never switch, both because the cost of switching is enormous, and because there is no way to coordinate/organize such a move.

ethanbond3• 6 years ago

Also... Excel is great. What exactly is "ahead" of Excel? People on here really underestimate how great of a product Excel is because of its cultural affiliations.

Joker_vD• 6 years ago

Malplezure, vi pravas :( Sed la espero neniam mortas plene.

anthk• 6 years ago

I think Interlingua is better as a Spanish speaker.

brutt• 6 years ago

English as Lingua Franca (Language of Western Europe) is less than century old. My mother studied French, because it was dominant language of that time.

FabHK7• 6 years ago

The world speaks English. It's an illogical and absurdly spelled language.

Man, the extent the aviation world has to bend itself to avoid the pitfalls of English....

"to" and "two" sound alike, and so does "for" and "four".

"Turn left t(w)o four zero" or "Climb t(w)o eight thousand"

To avoid that, the lingo is:

"Turn left heading two four zero", "Climb and maintain two eight thousand" (or, "Climb flight level two eight zero").

At any rate, switching to metric would be much better (If I'm 2000 metre high, and I have a glide ratio of 1:10, I can glide 20,000 metre or 20 km. If you're 5000 feet high, and you have a glide ratio of 1:10, how many nautical miles can you glide?), but for the reasons you mentioned, it won't happen anytime soon.

gsich• 6 years ago

Excel is quick and easy. From startup to import to calculation. You haven't imported a single line with Python or R.

TallGuyShort• 6 years ago

Also, on the list of dumb things that make me roll my eyes at Microsoft, interpreting the string "MARCH1" as a date and trying to normalize date formats isn't exactly the dumbest.

rorykoehler3• 6 years ago

Excel should belong to the commons at this stage

rat_1234• 6 years ago

This calls to mind a quote from Cormac McCarthy's Blood Meridian:

  It makes no difference what men think of war, said the judge. 
  War endures. As well ask men what they think of stone. War 
  was always here. Before man was, war waited for him. The 
  ultimate trade awaiting its ultimate practitioner. That is 
  the way it was and will be. That way and not some other way.

Just swap our war for Excel! "It makes no difference what men think of Excel, said the judge. Excel endures. As well ask men what they think of stone."

throwawaylabor• 6 years ago

The hyperbole here is amazing. You're saying that a 40 year old computer program that runs exclusively on windows is as un-changable as a _law of nature_.

The english comparison made in a peer comment is more apt, but wow.

The tech industry is so self-centered we think that Scientists should change their fields because of our bugs.

jordigh5• 6 years ago

Excel is older than many of us, so yeah, having it around for our whole lives does really make it seem like a law of nature. Sooner or later, all data ends up in Excel. I don't like it at all, but that's what happens.

As for this being an Excel bug: many, perhaps most people don't think it is. That's why it's so permanent: almost everyone likes it.

lifeisstillgood• 6 years ago

Nah, I plan on having HDF5 or Apache Arrow be the portable table of data and then build WASM based viewers - it's the excel killer but the war of attrition will be long !

pmarreck3• 6 years ago

OK then. Use Excel. But at least know the basics of Excel. Such as simply right clicking the column header and picking a different format so that it will stop auto-interpreting the input.

bromuro• 6 years ago

to people asking, "why do they use Excel?"

I often read comments on HN starting with “to people asking” or “to the people wondering”... and introducing a new discussion, usually on another similar topic, basically hijacking the whole discussion.

importantbrian2• 6 years ago

Whenever people ask me about getting into the field and if they should learn Python or R or Scala or whatever else, I always respond with you need to get really good with excel/Google Sheets and SQL. Then worry about everything else. I can't tell you how many times I've done an analysis I'm really proud of in something like R Markdown only to have the end user say that's great now can I get it in an excel? I just deliver everything in excel as a default now.

goatlover2• 6 years ago

good with excel/Google Sheets and SQL

Those aren't very good for cleaning and wrangling data, even if the output for analysis ends up being a spreadsheet.

guenthert• 6 years ago

Ah, just make the data file larger. No one is going to use a spreadsheet tool when there are millions of rows (I hope).

anameforauser• 6 years ago

“Newton had a great time for a long time with his description [of gravity], and then at some point it was clear that that description was fraying at the edges, and then Einstein offered a more complete version,” As for flat spreadsheets, they serve their purpose just like any other tool. I don’t think I heard people complain about spreadsheets for dealing with flat data, the same way no one complains about hammers to deal with nails. If we’re trying to deal with interconnected and enterprise resources we don’t maintain those resources in Excel. We store them in Enterprise Resource Planning software or build relational databases (which are as old as gravity). Nothing is wrong with spreadsheets, they’re the sweetest way to deal with quick data until we use them for things they can’t do.

Duller-Finite2• 6 years ago

Excel isn't the program of choice for most scientists and computational biologists, who typically use R, python, or command line tools. However, we often get data from other scientists or reanalyze data from other groups that can have these errors. It's so frequent of a problem that there are scientific papers about it [1].

[1] https://genomebiology.biomedcentral.com/articles/10.1186/s13...

Dumblydorr• 6 years ago

I had to use Excel for 100% of my publications and posters in medical services research. Either the data is in Excel or Excel is a tidy place to put data dictionaries. While I'd love to use only R, most of my collaborators wouldn't be able to use it, it's niche, whereas Excel is the lingua franca of data analysis.

xkgt• 6 years ago

I spent 30 minutes today convincing a Data scientist why she shouldn't use excel to store her interim data and that even an untyped data forms such as csv or json would be a better medium compared to an excel document.

aden1ne• 6 years ago

This ignores the reality that one will ultimately have to interact with people who have no understanding of any of these tools. Unless you happen to work in a pure computational biology group, one _will_ have to interact with lab workers, biologists with no training (or understanding of) in R or python, doctors, etc. All these people will know excel.

Duller-Finite2• 6 years ago

That's why I'm in favor of this change in nomenclature.

noobermin• 6 years ago

I feel that excel should still do the right thing which is annoying, but the change also helps people avoid confusing the names with dates, for example, in the excel sheet has dates in it well.

prepend• 6 years ago

Those tools aren’t exclusive.

I use python almost exclusively for data analysis but still open files in excel to view them for lots of reasons.

Nothing in excel is reproducible, but it’s still on all my computers.

Did an analysis a few years ago of the programs run At my organization by job series and scientists run a lot of excel.

jaclaz2• 6 years ago

Or perhaps prepending them when typing with either a single quote, apex or double quote (left/center/right align AND consider as Text), like all the rest of Excel users have done for the last 25+ years?

psychometry• 6 years ago

You don't understand the problem. Read the article.

proactivesvcs4• 6 years ago

I read the article and parent is correct in part. However, equally the article touches on the fact that many people may use a spreadsheet and not all will be correctly trained to use their complex tools properly, in order to ensure data integrity.

I consider this a failing not in software or Excel, but in education and professional standards. Scientists can be expected to understand and use their tools correctly, since not doing so will taint their data.

jaclaz2• 6 years ago

You don't understand the problem. Read the article.

Maybe I don't understand the problem, but I did read the article.

Care to explain what I don't understand?

so when a user inputs a gene’s alphanumeric symbol into a spreadsheet, like MARCH1 — short for “Membrane Associated Ring-CH-Type Finger 1” — Excel converts that into a date: 1-Mar.

I can assure you that if you prepend what you type with a single quote, or an apex or a double quote, what you type will be interpreted a text in Excel, respectively left, center and right aligned.

Even a number, i.e. "45 will result in 45(right aligned), and a small green triangle in top left corner and if you hover on it with the mouse it will pop-up something to the effect of "number formatted as text".

Shivetya• 6 years ago

It is just so fun when you have @ as a lead character for fields. You can do DATA-IMPORT from CSV and it is fine but if you just double click load a CSV from explorer it tries to interpret the data as a formula and randomly loses the @

I have not check myself the full list of special characters that cannot be loaded CSV style from Explorer but one should expect consistency with DATA-IMPORT functionality

Cactus20183• 6 years ago

Two more examples.

In a CSV with zip codes, Excel drops the leading zero: Boston, 02114.

A CSV with text ranges: 1-10, 11-20, 21-30... becomes 10-Jan and 20-Nov!

qayxc2• 6 years ago

That's because CSV is an untyped data format.

Why don't people use the import options available to them? You can select the precise data type of each column if you know the format anyway.

While the default choices that Excel makes are questionable at times, they're both known and can easily be overridden.

engineer_22• 6 years ago

Human language is more flexible than computer language. We send a few emails, agree on a new word, bingo bango problem solved. Easier than everyone learning a new computer program, easier than teaching everyone how to avoid the errors in Excel, just send a memo to all involved, and those who miss the memo will slowly catch on anyway. MARCH1 -> MARCHF1 seems like a good change and will not cause any confusion.

Cactus20183• 6 years ago

In a previous version of MS Excel, after opening a CSV file, Excel would silently write the 'interpreted' version to disk. So much trouble, simply from previewing a CSV.

ImaCake2• 6 years ago

I have had excel change date formats on me several times because of this "feature". I noticed it no longer does this sometime in the past year.

ocdtrekkie• 6 years ago

Another reason why not to "wait for an update for Excel" is that one of the perks of Excel is it's very long-lived data formats. People may be using Excel 2003 to work with you on a file you created in Excel 2019. You have to go all the way back to Excel 2000 to lose the ability to work on modern documents.

leecarraher2• 6 years ago

Final Jeopardy : Trebeck: You know what, how about you just write down a number, any number at all. Could be a 1 or a 2, perhaps 3... and excel you answered; A smiley face emoji, simply stunning.

Excel is no longer motivated by the original intention of a spreadsheet, and now caters to the lowest common denominator, a piece of graph paper. As such MS has shifted focus from doing calculation to text and graphics layout tool. white text copied from a terminal : white text white background you got it! comma separated numbers : default a long string with commas in it want a plot : it is in the insert menu for some reason, since plots and numbers are no longer excels raison d'être

jkaptur• 6 years ago

The date parsing being discussed has been the behavior for at least 20 years, probably more like 30.

Your comment about graph paper echoes a comment from a former Excel PM: "The gridlines are the most important feature of Excel, not recalc."

https://www.joelonsoftware.com/2012/01/06/how-trello-is-diff...

leecarraher2• 6 years ago

Nice, well there you go.

ubermonkey4• 6 years ago

MSFT did the same thing with Project when they introduced something called "Manually Scheduled Tasks."

Project is fundamentally a critical path scheduling tool, or at least was. Task A must finish before Task B, which must complete before Task C. If A is delayed, then that delay pushes B and C out, too.

This is what it's FOR, more or less.

Manually scheduled tasks don't move. They're set with whatever dates you give them, and do not move in response to delays or whatnot from predecessor tasks.

People wanted this because some (dumb) people insisted that "well, that task CANT move because it has to be done by then!"

This is akin to asking for the arithmetic engine to be turned off in Excel, because by golly you really need 2 and 2 to sum to 17.5.

wombatpm2• 6 years ago

Project scheduling on works badly if you have resources 100% dedicated to a project. Trying to acurately forcast with partial resource is an exercise in madness.

VonGallifrey• 6 years ago

People wanted this because some (dumb) people insisted that "well, that task CANT move because it has to be done by then!"

So? There are tasks like this that simply CANT move, why should a project management tool not have the ability to model that? If you don't like the feature you don't have to use it.

I get that you wouldn't want these fixed dates and if you can just plan and execute your project by yourself, but that is not always the case.

As an example: I had a project where it was really important for us to inspect the Aircraft Type (A321) into which we wanted to install our hardware and software. We where given an opportunity to do so by an airline during the maintenance of one of their A321. This was a fixed date. If we didn't finish our preparations before that maintenance date then we simply would not get this opportunity (or at the very least would have to wait a very very long time for the next opportunity). The Aircraft would simply not wait for us.

Not every task can move and just because you think they should does not make it so. Maybe there is more to Microsoft Project than just YOUR use-case?

LatteLazy9• 6 years ago

It's honestly amazing that Excel hasn't fixed this issue. It's pisses off an enormous number of users especially in basically any non-US country (even if 01/02 is a date, it isn't the second of January in most of the world...)

qayxc2• 6 years ago

That's because it's not an issue at all.

It's people using a tool without knowing said tool. You can disable auto-formatting (or even better yet - set the column data type) with a simple click.

LatteLazy9• 6 years ago

Even if you correctly format a column, excel will ignore that if it sees something that looks like a date. That's part of the problem: this isn't just automatic formatting, it's very aggressive and hard to turn off. Plus you cannot revert the changes excel has made: turning 01/02 is converted to an ibt in 46000 range. So reformatting the cell doesn't get the original input back, it just lands you with a bunch of ints.

Back in the day I actually wrote a function that would undo this for some sheets that people kept breaking...

pwinnski• 6 years ago

This is not auto-formatting, it's deeper than that. It is not actually possible to disable Excel's date recognition, although you can re-change the format of a cell after the fact.

catalogia3• 6 years ago

Excel is from an era when programs still catered to power users. Tools were made to have learning curves, ideally not particularly steep curves, but curves nevertheless. It wasn't expected that users would hit the app running, intuiting everything there was to know about the program in their first minute of using it.

The result is a rich deep program that users can grow into, rather than a shallow trivial program that optimizes for the noob experience and leaves power users out in the cold.

pintxo5• 6 years ago

For Germany this converts to the first of Feb, and this way of entering dates is just so convenient, as you can type the whole date just using the keys from the num block. We do actually support this format in our app for date entry, simply because it's so convenient.

LatteLazy9• 6 years ago

Maybe American dates are only foisted on us English speakers? Yet another complexity to be unravelled...

FrojoS3• 6 years ago

The usual way to write the date Feb 1st in Germany (and I think anywhere but the US) is 01.02 or 1.2. but not 01/02 And the . is part of the num block as well.

Also, data being interpreted different based on region/language settings is a sure way to end up with bugs, so I think its a terrible thing that Excel does this.

airstrike5• 6 years ago

Just type '01/02 or ="01/02" and get on with your life

LatteLazy9• 6 years ago

I think you've failed to grasp the issue, it's way more than just things I type sadly. It prevents the use csv files for many purposes. That stops excel users interacting with the wider ecosystem of file\data processing systems...

travisoneill1• 6 years ago

Is that January 2, February 1, January 2002, or January 1902?

wombatpm2• 6 years ago

Better learn to properly import data via csv. If you just double click you get the excel helpfulness. If you choose open you can specify the column formats. I load dates and zip codes as text and then format the columns correctly AFTER everything loads. VBA macros are wonderful if you have the same format over and over again. If I has a nickle for everytime excel munged a date or stripped a leading zero from a zip code, I'd be rich beyond dreams.

vsareto• 6 years ago

It's honestly amazing that Excel hasn't fixed this issue.

I'm surprised there hasn't been a dotfile option added yet

jtdev2• 6 years ago

Maybe a registry edit?

owl574• 6 years ago

I don't even know why anyone still uses this notation in today's connected world. 2020-08-06 is ideal. Russian standard (06.08.2020) is uglier (digits are not sorted by significance) but still unambiguous. But that slash horror? Why would anyone continue inflicting this on themselves and their peers!?

sixdimensional• 6 years ago

ISO8601

spuz• 6 years ago

How is "06.08.2020" unambiguous?

reportgunner6• 6 years ago

yes, YYYY-MM-DD HH:MM:SS is the only proper format.

It is sorted from the greatest unit (year) to the smallest unit (second). If you treat them as text and sort alphabetically they still get sorted from oldest to newest.

Other formats don't sort properly.

writing dd.mm.yyyy is like writing time ss:mm:hh

writing mm/dd/yyyy is like writing time mm/ss/hh

If I really have to put YYYY at the end of the date I use the 'dd-mmm-yyyy' format which excel translates based on client locale:

- 13-mar-2020 in enUS

- 13-bře-2020 in czech

LatteLazy9• 6 years ago

2020-08-06 I've just switched to 20200806. Let it be an int and be done with it.

DocG• 6 years ago

Because it's meant for human consumption. Day and month is usually relevant, while year is less so, so let's put it last.

IgniteTheSun• 6 years ago

I wish more people complied with ISO 8601.

    https://en.wikipedia.org/wiki/ISO_8601

Even better, wish everyone would standardize on:

* YYYYMMDD for dates

* YYYY-MM for months

* YYYYMMDDTHHMM for time where T is the capital letter T. Two additional digits can be added for seconds and then as many additional digits as needed for precision.

------------------

[Edited for format]

nikk1• 6 years ago

There are dozens of auto-formatting "features" that piss me off each and every day across all of Microsoft's products.

LatteLazy9• 6 years ago

You turn them off,forget about it, and then auto-update turns them all back on for you!

stjohnswarts9• 6 years ago

Even the default text editor on Mac OSX attempts to "fix" your typing. It's not just Microsoft.

CarbyAu• 6 years ago

My pet hate:

- use the mouse to select precisely the part of the word/URL/string you want for Ctrl-C purposes

- it autocorrects your selection to the whole word including a CrLf if it is nearby.

Aaargh!

mattnewton2• 6 years ago

Why hasn’t the field standardized around another tool? Is the list of excel plugins that something libreoffice couldn’t replace it, where they could just make add the options they need? My guess is the field is mostly lacking people interested in working on tooling like this?

pas4• 6 years ago

Because actual quality (usability of research, open access, reproducibility, data availability, sane exposition/introduction, proper methods, documentation and code) is not as important as producing a fancy manuscript.

Journals don't care, referees/reviewers might care, but they are unpaid and they usually don't want to rock the boat that much.

dnebdal• 6 years ago

It mostly has; most serious work is done in R and python, and there are a lot of supporting tools, documentation, and useful packages for both languages. It's not like the actual statistical analysis in your average paper was actually done in excel.

However, Excel remains a nice tool for "I'll just look at this CSV with the final results from this analysis, sort it by correlation, and see if any of the usual suspects are up top". And if the next step is "yeah, that looks fine - I'll just copy the top 100 genes into this convenient GUI pathway analysis tool", you're suddenly exposed to whatever Excel did to your data.

And as for "why not libreoffice", most researchers I personally run into are strong molecular biologists who've learned a subset of R for their uses; they're not really likely to go out and find libreoffice on their own. Besides, the writing process for papers includes sending drafts and spreadsheets to doctors and pathologists and editors, who are probably on hospital computers with a short whitelist of programs ... and I don't really want to debug subtle compatibility issues in the sort of garbage fire those documents can turn into.

danso2• 6 years ago

I personally dislike this behavior in Excel, as I'm accustomed to working with plaintext data and using pandas/strftime to get datetimes as I need them. But I also figure that my situation as a technical user is likely in the minority, compared to people who are using spreadsheets every day to do manual data entry.

That said, I completely agree with the tangential issue of U.S. dates being misleadingly different in format compared to non-U.S. Always an issue when teaching data/spreadsheets to a class with at least one non-American – but also a good reason to teach them the value of ISO8601 :)

reportgunner6• 6 years ago

It's not just about data entry. Entering dates properly in excel allows you to calculate with dates.

If you want to know what date and time it will be 476 days and 12 hours from today you can just do =TODAY()+476.5

This is very useful when the requirement is to 'happen every 10 days' or you're looking for '30 days in the past'

Excel has a very low barrier of entry compared to pandas while boasting an immense amount of power and features. I think it was not an easy challenge to keep it going over the decades.

US dates I'm a non-US person and I fixed this simply by changing my locale to enUS everywhere. It has an added benefit of not having weird translations of everything in random tools and excel functions not being localized to their cringy versions in my native tongue.

javier1234543212• 6 years ago

Honestly though, dates make more sense being month first, that way if you are trying to discover which one comes first, 10th of february or 10th of january, you only have to go to the first parameter in format 01/10 vs 02/10 instead of 10/01, 10/02. Logically, the most broad data item should go first.

RugnirViking• 6 years ago

Which is why most systems are yyyy/mm/DD when this matters

knorker2• 6 years ago

When does it matter to have month and day, but not year? Like the one time is for birthdays. The web is full of articles exactly dated with old day and month, neglecting year.

stjohnswarts9• 6 years ago

But you can select from dozens of formats in the cell setup? Not sure I understand the issue?

LatteLazy9• 6 years ago

You can select any format you like. Then, if you put in 01/01, excel ignores your format choice, displays Jan-01 and puts 46000 in the actual cell. If you re-select the text format, you get "46000". The 01/01 you actually entered is gone forever.

Now imagine you load a file with 1000s of values in the form AB/CD, many are trashed. If you save the file, you've lost the original data.

All because it might save some data entry drone 5 seconds to expressly make something a date.

There are then also issues about whether "01/02", assuming it actually is a date, should be the second of Jan (US) or the first of Feb (EU, UK, North America ex US, Africa and Pacific regions). In many places, based on some arbitrary and we'll hidden settings, you will get the wrong result.

I honestly think the only reason excel does this is to force you to use excel formats and make it harder to work with non-MS products...

T-hawk• 6 years ago

Excel is stuck. It can't change this behavior if it cares about backwards compatibility. There are uncountably many recorded macros and all sorts of scripts and data tools everywhere, that implicitly and inadvertently depend on behavior like this. Like Javascript's warts, you have to declare these as features and live with them.

Of course Microsoft has thought about this, it's silly to think they never have and we're smarter. Yes, this behavior is annoying and US-centric, they know that. But they also know that breaking compatibility with all these scripts and macros would be the worse problem in the larger picture. That picture is huge, it would be on the order of the scope of the Y2K effort to modify every code everywhere that's ever touched Excel dates.

Would you want Excel to introduce a "quirks mode" to handle this sort of thing?

loktarogar• 6 years ago

imagine if they worked with the industry to build in a set of relatively agreed upon defaults, like turning autoformat off for dates that matched a critera. then geneticists could activate the geneticist profile and excel works as they'd expect. if they encountered a file that broke, they could turn that profile off.

that's a fairly sophisticated solution, but it's just one of many potential approaches to fixing the problem.

mr_toad• 6 years ago

It can't change this behavior

They could easily make it an optional behaviour.

boublepop• 6 years ago

Excel doesn’t care about backwards compatibility. Random things break every single major update. If only the breaks were because they where fixing these extremely annoying “features” then it would be understandable, but no, they persist.

unnouinceput• 6 years ago

Quote: "There’s no easy fix, either. Excel doesn’t offer the option to turn off this auto-formatting..."

What kind of loopy Excel variant the article's author is using? It's right there in settings, you can easy stop auto-formatting. Yes, default installation has it on, but it can be turned off.

mplanchard• 6 years ago

From the article:

Even then, a scientist might fix their own data, but as soon as someone else opens the same spreadsheet in Excel without thinking, errors will be introduced all over again.

conductr3• 6 years ago

Also you can single quote ‘MARCH1 to keep it as string. This is safer as when you share the file you don’t know if the next person has the same settings change you described. Either way, it’s only 27 genes they had to rename. Seems like a good choice on their part to just rename them. Hopefully they’re doing this check during the naming stage for new genes.

taejo• 6 years ago

Now save that file as CSV (maybe because you want to process it with some non-Excel tool) and re-open it. Mar-1.

sseagull• 6 years ago

Can you explain where? I just booted up a relatively clean copy of Excel 2019 and can't find it after a couple minutes.

I see a few autoformatting options, but nothing corresponding to dates. The only options I see are similar to this:

https://www.journalofaccountancy.com/issues/2016/dec/how-to-...

epistasis7• 6 years ago

I do not believe it can be turned off, or at least I've never encountered a version where it could be turned off by toggling options.

Create a simple CSV file like

    Gene,value
    SEPT1,-7

Open it in Excel, and watch SEPT1 be silently converted to 1-Sep, with no way to return.

Exce is truly worse than most people can imagine.

t-c-h3• 6 years ago

As a bioinformatician I'm not too fond of this shift. Software should be sculpted around our needs, not the other way around. It's basically submitting to the fact that we've stubbed our toes hundreds of times to the exact same rock and never learned.

But then again, I use Excel rarely, usually only at the very end of some analysis (even then I prefer R/Python libraries for visuals). So I do have sympathy for wet-lab researchers who rely heavily on Excel.

lordnacho2• 6 years ago

The problem is that your average business user of Excel thinks type safety is a bug, not a feature. If Excel enforced types instead of guessing on your behalf, a lot of people would complain.

And it would be really hard to explain why it is sometimes useful to have constraints to someone who normally sees Excel's flexibility as its main strength.

I don't think I've ever seen a non coder use Excel in a sensible way: maintainable, easy to change, consistent meanings of entities, simple to understand. It's always a ball of spaghetti, even for pretty small projects. Loads of VLOOKUPs, external DLLs, buttons everywhere, vba files galore. Plus they lay out the cells haphazardly.

jrott2• 6 years ago

Yeah excel produces hairballs so easily. From a coder perspective I've only seen excel workbooks that are bad or terrifying.

On the other hand a ton of people that don't write software for a living and have no interest in code manage to produce things that help them do their job and automate a ton of tedious stuff.

lordnacho2• 6 years ago

Most people don't write novels or poetry for a living either, but we still need some grasp of basic writing skills like grammar and structuring. We're already moving towards a future where a lot of people have coding as part of their job, and we should expect them to know a few things.

dgb23• 6 years ago

From a coder perspective I've only seen excel workbooks that are bad or terrifying.

I think this might be a type of bias:

The Excel sheets I usually look at as a programmer are from customers who want me to write an application for them. Typically because the conventional means (Excel sheets, directory structures, emails etc.) become unmaintainable.

So we end up seeing the stuff that is bad for w/e reason. Often a combination of complexity, inconsistency, usability and sheer mass.

ynodir• 6 years ago

Havent read the article, but come on. Tools should serve us, not the other way around. It'd be enough just to change the affected cells' type.

jmkjaer• 6 years ago

Havent read the article

Please do. The article has a paragraph that addresses this.

curiousllama2• 6 years ago

Excel datetime functions are garbage. you have to explicitly, manually tell it not to format things as dates, the most destructive data type, but it doesn't act that way for other formats (e.g., $ doesn't turn things into accounting format).

That said: every datetime function I've ever written is also garbage so... glass houses, I guess?

bronzeage2• 6 years ago

Which is exactly why feeding all your input into a trash input parsing function by default is a horrible idea. If dates handling was just 1 extra button you click, the problem wouldn't exist. Overzealous default behavior, the #1 sin of Microsoft.

Brett_S• 6 years ago

If the scientists had asked someone who knew Excel well, then they would have been told to prevent autocorrect from running enter ’MARCH1 with the apostrophe at the start.

mrunkel• 6 years ago

This. Why does nobody know this?

angel_j• 6 years ago

There must be a net benefit to using this software, right? Otherwise somebody in charge would make the decision to use something else, right? It’s not like they are forced to use MS excel, right?

tomp• 6 years ago

For example, HECA used to have the gene name ‘headcase homolog (Drosophila),’ named after the equivalent gene in fruit fly, but we changed it to ‘hdc homolog, cell cycle regulator’ to avoid potential offense.”

Ugh... everything is politics now.

krastanov• 6 years ago

As the sentence before the one you quoted explains, this is not done for politics. It is done because clinicians have to be taken seriously by the parents of the child having a mutation in that gene. Saying "your child has a headcase mutation" ends up causing defensive reactions instead of discussing treatment. Maybe that is an irrational reaction on the parents' part, but not everyone is super rational the first moment they learn their child has an illness.

_petronius• 6 years ago

As Thomas Mann said, everything is politics. I would add that has always been the case.

You can get frustrated about that fact, or you can use it as an opportunity for learning about how and why that is so, and gain a deeper understanding of the tightly linked systems of power relationships all around you.

takluyver5• 6 years ago

Programmers love to complain about Excel, but we happily use YAML, which has essentially the same footgun: certain strings (like 'on') need quoting if you don't want it to interpret them as something else.

fabian2k• 6 years ago

We programmers also complain a lot about YAML, though maybe not enough and not as much as about Excel. But some YAML footguns like the country code for Norway being interpreted as a boolean are reasonably famous, and I think widely regarded as a bad idea.

takluyver5• 6 years ago

To be fair, there are few things programmers don't complain about. But YAML still seems to be very popular as a configuration format for new tools, e.g. CI services.

aitchnyu• 6 years ago

Is there a YAML linter that enforces a safe subset of YAML so that unquoted "no" never makes it?

the_imp• 6 years ago

This was fixed in YAML 1.2, which was released in 2009. Don't blame the spec, blame the applications that use ancient libraries.

DarkWiiPlayer2• 6 years ago

YAML offers a simple solution though: just quote all one-word text and, more importantly, quote all text you don't manually write into the YAML file.

Also, the main problem is that excel changes your input instead of just misinterpreting it. The difference being, the original will be gone.

takluyver5• 6 years ago

You could also start every Excel cell with ' . But it's really convenient not to quote stuff, so that's what everyone does, both in Excel and in YAML.

reportgunner6• 6 years ago

excel changes your input

That's a feature though. Coincidentally it only happens when you paste the data or if you messed up the generation of the file (didn't escape the column you want to leave untouched).

To me it seems like a case of you're holding it wrong. (which I don't wholeheartedly support)

mattnewton2• 6 years ago

If I use those words a lot I will consider changing from YAML since it might be the wrong tool.

recursive• 6 years ago

we happily use YAML

Don't include me in that group.

mixmastamyk• 6 years ago

https://hitchdev.com/strictyaml/

elwell• 6 years ago

Programmers love to complain about Excel

In Apache POI HSSF/XSSF (Java Excel API), the HSSF actually stands for "Horrible Spreadsheet Format".

As a fun example, note that we need isSheetHidden() and isSheetVeryHidden() functions. (http://poi.apache.org/apidocs/4.1/org/apache/poi/ss/usermode...)

rkachowski• 6 years ago

My mind is blown that Excel's usability is so bad that the representation of the human genome itself has to adapt around it's undesired behaviour.

As in, the history of genetics research is now irreversibly linked with the shortcomings of this one software product, which just happens to be incapable of describing the genetics of the organisms that created it.

ep103• 6 years ago

My favorite is still that Excel can't handle dates before 1900

mmcgaha• 6 years ago

I hate to sound like a salty old IT guy, but here we go. It is not the fault of Excel that people are using it wrong. They have the ability to import the data as text but they skip that step all together. If the user does not say up front what the column is, Excel has to guess. If Excel didn't try to guess, someone would be making a comment on how bad usability is when an obvious date field was getting interpreted as text.

WorldMaker2• 6 years ago

It's also behavior that goes back to the Ancient Times and predecessors such as Visicalc and Lotus 1-2-3. Even ancient ones will tell you if you need to enter a thing and it has to be text and only text precede it with a quote mark, ie 'MARCH1, just as you would precede a formula with =. It's Spreadsheet 101 knowledge dating back many decades.

The clickbait headline is fun, but the real headline is more like "Scientists find it easier to rename things than learn the basics of data entry in the tools they use".

throwaway936482• 6 years ago

Excels usability isn't bad though. Which is why everyone uses it. It falls down in this (and lots of other cases) but if you want to see what good usability looks like, look at what people use.

throw_m239339• 6 years ago

Everyone uses Excel because it's a tool everybody learned at University or even highschool, nothing more, it's not because it's good at all.

It's a terrible app but Microsoft managed to shove it everywhere in education. Same for Words.

Unfortunately Libre Office and co tried to reproduce the terrible UX of both software, while coming up with inferior functionalities because they had to try to maximise compatibility with these MS tools, time not spent on providing actual useful stuff.

Access is a bit better and I wish more non programmers learned basic SQL instead of Excel.

mnw21cam2• 6 years ago

The problem is it isn't just dates. If you load a list of genomic variants (read: mutations) into Excel, then the standard way to describe whether a person has the variant or not is to use "0/0" for no, "0/1" for yes heterozygous, and "1/1" for yes homozygous. Guess what gets auto-converted into the first of January.

mattmar96• 6 years ago

If this kind of thing amuses you, check out the book Humble Pi by Matt Parker. Its a collection of stories about maths errors. Lo and behold, most of them happen in Excel.

DarkWiiPlayer2• 6 years ago

Reading this just makes me incredibly sad. This feels so dumb and backwards... Why do people even still use excell at all?

epistasis7• 6 years ago

What are the alternatives?

I tend to use "less -Sx 20" to preview data files but try explaining that to a non-CLI user on Windows.

jpindar6• 6 years ago

Because their manager wants the data in Excel, because their managers used Excel, because THEIR managers used Excel.

It's managers all the way down.

glofish3• 6 years ago

Infuriatingly,

the paper announcing the new guidelines of renaming genes, a work of fundamental importance to all scientists in the world, cannot be read without an expensive subscription to the journal.

https://www.nature.com/articles/s41588-020-0669-3

Thanks science (sarcasm!)

rolph7• 6 years ago

copy paste the DOI into scihub

LordDragonfang• 6 years ago

That's entirely missing the point.

racl101• 6 years ago

Well, it's funny to know that even the people doing the most cutting edge stuff are also getting fucked in the rear by that piece of shit software Excel.

Seriously, I hardly trust the program anymore. I always need to get the truth from Python and Pandas.

alistairSH• 6 years ago

Ugh. Why is "MARCH1" interpreted as a date at all? Does anybody use that as shorthand for March 1?

Gatsky• 6 years ago

Ah, it’s a shame they are doing this. Finding garbled gene names is a quick way to pick a poor quality paper when reviewing.

snow_mac• 6 years ago

Why Doesn't Excel allow you to change date formatting? or column formatting?

emteycz3• 6 years ago

It does.

jtdev2• 6 years ago

Excel is the 2020s technological progress stunting equivalent of the fax machine.

jbaber• 6 years ago

I don't see as pitiful that these non-technical* people can only use Excel.

I see as glorious that I'm able to get something close to real database tables out of non-technical people as long as they're in Excel.

I once populated a pretty sophisticated database by giving a bunch of computer semi-literate people Excel tables with only headers and simple instructions. They respected foreign key constraints, etc. that I could never have directly explained.

*in the software engineering way

csours• 6 years ago

Excel also clobbers long numbers, which has caused all manner of confusion for serial number audits.

To prevent this, set your whole sheet to Text before any other steps.

pkphilip• 6 years ago

Couldn't they have just used a prefix. Eg: G_MARCH1 instead of renaming the whole set?

mcv7• 6 years ago

"Why, exactly, in a fight between Microsoft and the entire genetics community, was it the scientists who had to back down?"

Back down? Or pick a better tool. If Excel proves to be an unreliable tool for your job, use a better one. Alternatives exist, ranging from Google Docs, and LibreOffice, to simpler light-weight spreadsheets. Or possibly more specialist tools.

Why does everything always have to be put in Excel if Excel is such a poor tool for so many things?

acid__• 6 years ago

Excel may have failed in this specific task, but let’s not pretend like its functionality doesn’t run circles around Google Docs and LibreOffice.

Excel is a “pretty darn good” tool for 95% of tasks. If your work has highly varied workflows, then that flexibility more than makes up for its failures on the last 5%.

If you have very specific workflows on the other hand, you may find value in replacing Excel with a specialist tool. But let’s not pretend that specialist tools don’t also have their own shortcomings; at best they’ll achieve 99.9% coverage of tasks.

reportgunner6• 6 years ago

From my point of view Excel hits a sweet spot between 'very simple tasks' and 'very complex tasks'.

otherme1234• 6 years ago

LibreOffice AND Google Docs fall in the same exact spot than Excel here. Try it. Type "SEPT-2" or "SEPT2" in any of them and see what happens.

erAck4• 6 years ago

LibreOffice Calc: "SEPT-2" is a date, "SEPT2" is not.

toast0• 6 years ago

Better tools for managing genomic information are probably out there, but if everyone picks Excel (or workalikes that all do automatic date formating too), then hoping people switch is tilting at windmills.

Unilaterally declaring you will not use names that confuse Excel will fix the problem (until we get new month names, anyway), and not require anyone outside their sphere of influence to comply.

I had the same situation at work, as an aquired company we had servers named devX, the new parent had servers named devX and pushed ssh config files to our laptops that made it impossible to connect to our servers; we asked them to fix it the first couple times, but eventually renamed our servers to vedX to avoid the issue. It wasn't the right solution, but it was a stable solution and let us get on with our lives instead of fighting with IT.

tqi• 6 years ago

Characterizing this as a "fight between Microsoft and the entire genetics community" is... a choice. But I guess Tech is this season's "big bad", so every subplot has to tie back.

asdff• 6 years ago

The vast majority of people who use tabular data a lot in biology are already just using python or R primarily, and most do this work on a cluster. This article is what happens when you send your .csv to a PI who hasn't bothered to brush up on any computer literacy after somehow mastering punch card programming, or the undergrad whose breadth of understanding beyond the web browser starts with MS office and ends with g suite.

HiFaraz• 6 years ago

But they did pick a better tool: they changed the name. Nomenclature is also technology.

danso2• 6 years ago

The article links to a paywalled Nature article [0], titled "Guidelines for human gene nomenclature". I googled that title to find a free and open version, and came across what seems to be the official page for HGNC's (extensive) naming guidelines [1], though what's currently published seems to be an older standard, originally published in 2002 [2]

[0] https://www.nature.com/articles/s41588-020-0669-3

[1] https://www.genenames.org/about/guidelines/

[2] https://pubmed.ncbi.nlm.nih.gov/11944974/

bronzeage2• 6 years ago

Honestly, Microsoft should fix Excel to stop corrupting data by default. This is 100% Microsoft's fault that an international organisation resorts to workaround renaming things because they needlessly parse and modify input in a default configuration.

You can also say pretty much many of Microsoft security issues over the years boil down to their programs needlessly overthinking and parsing perfectly valid input, for obscure reasons.

arkanciscan• 6 years ago

I wish we could rename addresses with "drive" in them so that Instapaper and Pocket stop reading the abbreviation as "Doctor".

"There was a shooting today on The 800 block of MLK Doctor"

vikramkr6• 6 years ago

A lot of people are attacking excel in this thread, just remember to give a fair share of the blame to people naming genes as well. These are meaningless names and changing them is frankly easier than changing a feature in excel (that the finance folk probably don't want changed). And its a good excuse to clear up some of the egregiously silly disease related names as well so we don't tell parents their kid is suffering from a debilitating mutation in luke-Skywalker-like upside-down cantaloupe 12b or whatever.

rolph7• 6 years ago

no these are not meaningless names, there is undue confusication in the case of the more contemporary names such as [Sonichedgehog]

these are not meaningless lables https://en.wikipedia.org/wiki/Sonic_hedgehog

mentioned eslewhere in this thread is a nomenclature that allows one to easily find notes referring to the gene from your research library. It was a new cadre of young upcoming scientists that decided to break with tradition and use something familiar to lable genes according to game characters or pop icons.

vikramkr6• 6 years ago

They're tags and you end up with multiple names for the same gene. They might as well be replaced with an arbitrary string of numbers- those are equally easy to do a quick Google scholar or pubmed search with. You don't lose anything of value by changing the name. In chemistry amd organic chemistry, the proper IUPAC name of the compound also tells you its structure. It tells you what the compound is in a real physical sense. If you go to someone thats never heard of a compound d and you give them the full IUPAC name, they can draw it. You can't change those names without losing meaning.

You go to someone who's never hears of the hedgehog signaling pathway and ask them what SHH is, they're not gonna be able to give you much.

yters• 6 years ago

Why not an excel plugin to fix the problem?

nesarkvechnep• 6 years ago

https://thedailywtf.com/articles/another-immovable-spreadshe...

alkonaut• 6 years ago

Automatic type conversion is the root of so much pain. It doesn’t just apply to C# or javascript apparently.

komali22• 6 years ago

In excel, can you set the a column to not format to dates? I think you can do that in google docs for example. Why don't they do that?

LatteLazy9• 6 years ago

It's actually worse. If you expressly set the format of a column to text, then put 01/01 in it, you still get Jan-01 and a field value of 46000ish.

komali22• 6 years ago

That's horrible UX - why on earth is it that way?

rolph7• 6 years ago

when i was undergrad there was an academically priced package for about 500$ you get the entire suite of word excel access powerpoint bells whistles and nice rugged carrier box for all the tomes. i dont groom well i used it until taking an assistants position and we used ...lotus, symphony,and norton utilities and some funky in house coded version of a Dbase, and IC4 [some inventory control utility]

Excel was nowhere in sight, and that was thirty years ago.

If someone wants a data set i give them a .csv it is then thier fault if they plug it into excel.

24gttghh• 6 years ago

Just change the data type of the column in question??

In the article it even mentions this and links to how to change it:

Excel doesn’t offer the option to turn off this auto-formatting, and the only way to avoid it is to change the data type for individual columns.

https://www.youtube.com/watch?v=SppKiKIdCkI&feature=youtu.be

zw123456• 6 years ago

There is a super simple work around for this and it is all over the web if you search for it. Open a blank sheet, go to data, click on From Text, that allows you to import data identifying the type for each column. Yes, it is a couple more clicks but for 99% of people the auto-formatting is probably nice. I have shown this to people a million times at work and they are always amazed. But honestly a quick google search provides the solution.

patgrdj• 6 years ago

So all that noise for people that don't know to change the data type of a column?

jpeloquin• 6 years ago

Most of the reactions seem fall into three categories:

1. "You (the individual) should stop using Excel". Good advice. As the article mentions, though, if you ever send a tabular data file to someone else to edit, there's a fair chance they will open it in Excel and corrupt the data. Excel's CSV import/export cycle has even more traps than using xlsx consistently, so in some sense individual avoidance of Excel makes the problem worse. You could send SQLite files or somesuch so your colleagues can't work with your data, I suppose.

2. "You (all scientists / whatever non-programmer group) should stop using Excel for data analysis". True, but getting everyone to change is hard. Both top-down (thou shalt not use Excel!) and market-driven (Airtable! LibreOffice!) haven't worked so far. Hopefully there will be more progress on this front.

3. "Scientists should learn to use their tools". Even if a user knows all the scenarios in which Excel corrupts data, it cannot in general be stopped from doing so. If you set column types up front, type every value manually (no copy-paste) & save and check on each edit, and then never re-save the file again or let anyone else re-save it, you're safe. Probably. I sort of expect a bunch of replies saying this isn't safe at all. But if the file is edited, you need to check the whole thing. Eventually an error will slip through.

The only way I've found to safely work with Excel files, or files that might be edited in Excel, is to put the data files under version control and always check the diffs. Often someone else's import/export cycle will change the quoting style on the whole file. In this case, the work needs to be repeated so the diffs are clean and the changes can be verified. This is a necessary but very obnoxious part of this process. Version control targeted at non-programmers and non-text files would help a lot.

There are also tools to to flag potential data corruption errors [1], but error detection in the absence of the original data won't be perfect. And if it is manually run, it won't be run consistently. Most people don't have continuous integration pipelines for their data.

Excel is very powerful and has a lot of potential to improve non-programmer productivity. It is unfortunate that avoiding data corruption is such a minefield. The situation could be significantly improved by anyone with a decent product and a great marketing strategy.

[1] http://maplab.imppc.org/truke/

justinsaccount• 6 years ago

No one knows how to use excel.

https://www.youtube.com/watch?v=0nbkaYsR94c

superjan• 6 years ago

Tangent: I once tried to help a friend (non-coder) with his slow excel sheet. He was using string search in excel to match virus DNA fragments. As you’d expect, it was way more complicated than neccesary, and likely buggy. I searched around for something better. But if you cant code, its a really big step to ditch your excel and learn a Programming language. But there is no such thing. It’s understandable that people stick with what they know, but the results are awful.

Metacelsus• 6 years ago

This happened to me! Someone sent me a spreadsheet where OCT4 was changed to "October 4"

phendrenad2• 6 years ago

If the genomics community is really so big, they should just make their own file format (.gcvs or something) and make an Excel plugin that treats it as literal text cells.

alphanumeric0• 6 years ago

Research software dev here. This came as a huge shock to me when I started at my job. I work with very smart, dedicated people performing cancer research, why would they put up with this affecting their productivity? Humans really are adaptable creatures.

After a few months of working there my boss handed me 3 or 4 Excel spreadsheets to compare to ensure a recent change I made hadn't affected our data (we don't have much in the way of automated tests either). As a software developer, this was a deeply troubling request.

One option was to load them in to database tables so that I could perform SQL queries against the data (Postgres has COPY that works with CSVs), which isn't hard and probably the path most people should take, but I didn't want to write table definitions.

I ended up using https://github.com/BurntSushi/xsv (I am not affiliated with the project in any way). It's a command-line tool written in Rust that performs queries/joins/manipulation/basic analysis against CSV/TSV files. While not as analytically powerful as Excel or Postgres, I was able to verify the data was good and pipe out results into another file without writing any custom code, and without opening a single file.

MaxBarraclough• 6 years ago

Were the files expected to be identical? If so, diff would have done the job.

Perhaps not directly relevant, but the lesser known GNU Recutils looks neat. Perhaps some day I'll find an opportunity to try it out.

https://www.gnu.org/software/recutils/manual/recutils.html#I...

teruakohatu• 6 years ago

Have you checked out the tidyverse suite of packages on R? The tidyverse philosophy is don't make assumptions about data so you get detailed error messages when one row probably was not parsed correctly.

c3534l• 6 years ago

I've always stood by the principle that a computer should never "correct" human input without asking and this is a great example of that. There should be a prompt asking if you want to convert the data, and there should always remain a way to undo "corrections," but it should never happen without the user knowing about it or wanting it to happen.

njarboe2• 6 years ago

"There’s no easy fix, either. Excel doesn’t offer the option to turn off this auto-formatting, and the only way to avoid it is to change the data type for individual columns. Even then, a scientist might fix their own data, but as soon as someone else opens the same spreadsheet in Excel without thinking, errors will be introduced all over again."

These are easy fixes. I have this problem using excel. Just format your data as text. If you do that and give the excel file to someone else the errors will not be introduced again. The quote above is at best misleading and I would say just wrong. The problem only happens when you import data in a non-excel format like comma delimited. In that case you have to click on a button when importing to excel to import it as "text" instead of automatic excel formatting. It would be great if you could set this as the default (maybe you can, but I don't know how), but it is something you get used to very quickly.

Think of all of the financial people that use excel that would have this same problem. I'm sure there are some mess ups, but I don't think that industry has the same endemic problem with this excel file formatting issue. There are definitely problems with using excel, but I don't think this would have to be one of them.

Edit: Of course it is not an easy fix to get everyone to use Excel correctly, but these types of bad data errors come from all over the place if the person using the data is not careful.

danielecook• 6 years ago

I'd love to see a better alternative to Excel geared more towards scientists. While it is true that R and python should be used for analysis, its often hard to escape a tool like Excel for data entry or browsing datasets. It would be nice if there was something out there that was faster and could handle larger datasets, and had features designed to help with data entry and browsing.

I am envisioning something with double-entry data checking, enforcement of data types, and rules to check validity. Equally important would be the features it would lack - no tools to color or format text, no variable font sizes, no embedded images, etc.

nayuki• 6 years ago

https://www.youtube.com/watch?v=yb2zkxHDfUE&t=696 "When Spreadsheets Attack!" by Stand-up Maths

iaw• 6 years ago

My favorite excel feature is when it randomly flips a 0 to an 8 or vice-versa in really long integers. Took a long time to figure out what was going on with that one.

randompwd• 6 years ago

So if someone stopped at the byline, they would have thought it was 'excel dumb' issue:

Sometimes it’s easier to rewrite genetics than update Excel

rather than y'know, scientists cant properly use a tool they're using.

bad journalist.

edit: it even seems some of the commenters here stopped at the byline. quelle surprise

Florin_Andrei• 6 years ago

Bruford’s theory is that it’s simply not worth the trouble to change

Not worth it for the Microsoft power brokers. Very much worth it for a lot of people.

And so the world is sometimes shaped by the narrow money interests of a very small group.

dekhn3• 6 years ago

In grad school I studied a gene which at the time was called Oct1 ("octamer binding protein 1"). My main problem was literature searches, which often found "OCT-1" (organic cation transporter-1). Genomic naming is a total mess, I found it easier to just mentally compute the md5sum of a name, then memorize the first few digits (only need about 8-10 hex digits).

DMLoeffe• 6 years ago

just mentally compute the md5sum of a name

What are you taking about lol

aqme28• 6 years ago

mentally compute

I think he means "remember"

foresto• 6 years ago

Somewhat tangential:

If you use software in American English but dislike American date and time formats, you might see if your OS respects the en_DK locale setting. It works on most recent linux systems I've tried.

$ LC_TIME=en_US.UTF-8 date '+%x %X'

08/06/2020 01:00:00 PM

$ LC_TIME=en_DK.UTF-8 date '+%x %X'

2020-08-06 13:00:00

Eyas• 6 years ago

I'm surprised no one here mentioned backwards compatibility as the likely ultimate reason Microsoft hasn't fixed this by default yet.

As others have mentioned, the "fix" of turning off auto-formatting is already available. But many here are wondering my Microsoft hasn't fixed the default.

I assume it's for the same reason Microsoft Excel purposely claims the date 1900-02-29 is valid, to be compatible with Lotus-1-2-3. I assume many legacy spreadsheets would break unpredictably if interpreting values as dates by default changed.

hermitcrab2• 6 years ago

Excel is an amazing tool. But it also has some significant shortcomings:

* A well known tendency to mangle date and gene data under the guide of being 'helpful'. * Easy to making mistakes when cutting and pasting cells. * Difficult to see what is going on in a spreadsheet. * Poor handling of CSV files.

Some of these shortcoming are inherent to spreadsheets. Others are specific to Excel, but hard to overcome due to the weight of backward compatibility.

I have written a product for transforming and analysing tabular data (https://www.easydatatransform.com) that tries to overcome these issues:

* Doesn't change your input data file. * Doesn't re-interpret your data, unless you ask it to. * See changes as a visual data flow. * Operations happen on a whole table or column. * Good handling of CSV files.

Also it doesn't try to do everything Excel does.

It is a fairly new tool. Would appreciate some feedback.

zuno• 6 years ago

Thanks. I just checked the video. Looks neat! Excited to try it out.

tucaz• 6 years ago

Website looks great. Will try shortly and I'm more than willing to give you my money if it delivers. We need a product like this.

Edit: great call out to 7 days of non consecutive use

gverrilla• 6 years ago

I'm a business guy, so I don't know much about it, but looks very useful and easy! I have been off the game for 5 years now, but I used to run a magento ecommerce, and I think your software might have helped managing products listings and stuff like that at the time. You might wanna take a look at this costumer segment, particularly small and medium businesses. They probably have a process for this already, but your software might be a good replacement, even though they won't be actively looking for it because they already have something that works. Good luck!

hermitcrab2• 6 years ago

Thanks. I will see what I can find out.

ezekiel68• 6 years ago

I have heard of life imitating art -- but I must say I find it a little eyeroll-worthy to contemplate that otherwise bright grad students and other researchers were unable to push through the struggle to understand and adapt to the quirks of MS Excel that many of the rest of us (in other fields) have needed to. I would have sooner expected Microsof to have added a "gene name" cell formatter option than for this to have occurred.

vmchale• 6 years ago

Load-bearing bug :p

stjohnswarts9• 6 years ago

Who actually uses a tool for critical work and doesn't understand it enough to get around such annoyances? I have a bunch of gripes about various languages I use but I know that they are standard issue and nothing I can do about it so I work around them.

xchip• 6 years ago

Why are they usin excel on the first place?

husamia• 6 years ago

This was right decision to make. Gene names aren’t as important as being able to handle them properly. Names should be easily handled by software.

xenonite• 6 years ago

I suppose it is not even enough to check and change "JANUARY1": in Spanish it is "ENNERO1". So one needs to check the word in every language for which Excel exists. And in every language that Excel supports in the future.

Scientists rename human genes to stop MS Excel from misreading them as dates

Related Stories