Autocorrect errors in Excel still creating genomics headache

https://www.nature.com/articles/d41586-021-02211-4

by tchalla • 5 years ago

80 106 5 years ago

OptionX3• 5 years ago

Can anyone with first hand experience on this tell shed some light on why stick with Excel versus using python, R or what have you to treat the data?

minikites• 5 years ago

Given that these are relatively unskilled users, would switching away from Excel increase or decrease errors? I don't think it's a given that errors would decrease by switching away from Excel.

instagraham• 5 years ago

I think there is a difference between the errors. Excel autocorrect errors are easy to miss as it is the software making an arbritrary decision over your own.

Errors made from using a new , presumably neutral software, would be the kind you are supposed to catch as a researcher.

There is obviously scope for error in everything but Excel's user-hostile methods are not justified by this.

an_d_rew2• 5 years ago

Switch to what?

Excel is everywhere.

People are familiar with it. The same people are often not familiar with, and have never used, and may even be confused by “notepad.exe”.

blackbear_• 5 years ago

Anecdotically, I was talking to an acquaintance who decided to pick up R as a replacement for excel for data analysis and they were positively blown away by the possibilities opened by the new mindset that came with programming.

So yes, maybe these "relatively unskilled users" will struggle for a few weeks/months, but it will be a net positive change as many of them will see immense benefit after some tinkering.

an_d_rew2• 5 years ago

Because an absolutely enormous number of people involved in all ends of genomics and biomedical science have absolutely no inkling whatsoever about programming.

Furthermore, Microsoft office is installed everywhere everywhere everywhere… like it leave it love it or hate it, it doesn’t matter… it’s true (at least for huge swaths of the demographic).

So the first thing most people learn is excel and the last thing most people use is excel. It’s a database, it’s a spreadsheet, you can even use it as a word processor if you want to. And some have.

I’m not saying you could and I’m not saying you should, I’m just answering as to “why“.

fartcannon• 5 years ago

Yeah. At some point though, people who want to do something that requires python or R, but refuse to learn it, and then use Excel (and make mistakes like the article mentions) should reconsider their choices. This is really out of character for me, but in this instance, relying on excel to do everything is a mistake by the user, not Microsoft's ridiculous data destroying import function.

Use the right tool.

OptionX3• 5 years ago

Even newcomers?

I've had my education in a engineering focused campus so that may skew it, but even not IT related area had programming courses to, at least, learn the basics.

Do the newer researcher still show reluctance to move from Excel or is it an old guard kind of deal?

gerdesj• 5 years ago

"That’s something Purdie says she doesn’t have time for. She has adapted to Excel’s quirks, adding apostrophes before commonly affected genes to prevent the conversion, or pre-formatting spreadsheet cells before importing data. “It’s one of those things that I just accept,” she says."

qayxc4• 5 years ago

Wait, if she knows how to avoid the issue, how is it still an issue?

enaaem• 5 years ago

It might be easier to teach people how to use excel correctly with for example power query.

happytoexplain• 5 years ago

Is there any reason to suspect that the primary factor is something other than the difference between an extremely featured end user GUI and programming?

gpapilion2• 5 years ago

I took a programming for math majors course in college, and the largest challenge for the class was understanding procedural looping to process data sets. Understanding a for loop or a while loop really was a hard concept for someone to understand. We didn't cover any real data structures, and everything was a 2 dimensional array.

Excel, and most spreadsheets for that, avoid some of the basic control structures beginners find challenging. Many functions are basically map and reduce functions so you typically are getting the same number of cells back, or one. Often maps are done visually in the spreadsheet with a formula being applied to every cell in a column.

delosrogers2• 5 years ago

From my experience Python/R can be great when you have large scale analysis to do but if you have a task that requires more manual fiddling with the data then it’s much nicer to use Excel.

cm21872• 5 years ago

When you have done programming for a while and it is mostly muscle memory, it is easy to forget how much it is an impenetrable black box to someone who has never done it.

Try to tell someone who just needs to get a task done that he needs to spend the next 3 months learning programming from scratch vs using excel.

breakfastduck8• 5 years ago

Tough shit, though. Thats what they're being paid for.

"A bad workman always blames his tools" has never been truer than this situation.

jpeloquin• 5 years ago

As a biomedical researcher who prefers using python & R over Excel:

(1) Graduate students (who are the bulk of the scientific labor force) usually have some training in python, R, or Matlab, but are seldom practiced in applying it to real-world work. So if a lab standardizes on python, R, etc. the senior people have to do a lot of extra work to support the programming, rather than doing work with intrinsic scientific value. It's easier to teach the Excel quirks than to teach effective programming practices. Excel quirks are concrete, and "good practices" in programming are vague and situational.

(2) Python & R have their own pitfalls. It's easy to apply the wrong filters to a data set and compare subsets that aren't what you think you're comparing. Although when they go wrong, they go very wrong, and this is easier for a supervisor to notice.

(3) Excel has rich text formatting, graph embedding, and data types, so it's very useful as a human-readable data interchange & summary format even if you never do computation in it.

IMO neither programming languages nor Excel are a great fit for data analysis. Something purpose-made, like JMP or even GraphPad, is probably the better choice in most situations. Programming gets you automation but at the cost of high complexity. Since you still need to look at and think about the data there's a limit to how much time automation can save (Amdahl's law).

taeric• 5 years ago

The same line of questioning can easily land in "why use csv?"

It is a terrible format that has very few redeeming qualities. It happens to also be the most widely used one.

gpapilion2• 5 years ago

Autocorrect(and predictive text) has reached an interesting milestone, good enough that you mainly rely on it, but occasionally it distorts the data you intended to send. Disengaging the technology isn't easy, or even understanding field types it shouldn't correct(everyday my work VPN auth window tries to autocorrect my username). There is so much context that is unavailable to the computer, and there is no good way to provide it.

I think it has interesting implications for self-driving, since as far as I can tell we're at an earlier but similar stage in its development; works for simple situations, likely to drive you into a wall for no reason. We build so much trust in these systems, we stop paying attention to what they are doing, and then pay the price when it behaves in an unexpected way to the user.

soco• 5 years ago

I get bitten by Excel's auto-correct all the time and I work nothing related to genomics. It's in my opinion just a dumb decision of Microsoft to not let you disable all those smarty feats (like, thinking you have a date if your string kinda looks like one)

planet-and-halo• 5 years ago

Serious question, is this a viable area for a startup product? Obviously nothing will easily displace Excel for general spreadsheet work, but I could see room for a domain-specific spreadsheet that has core features and supports just a basic set of functions relevant to genomics, + a "we won't fuck up your data" feature.

NullPrefix• 5 years ago

a "we won't fuck up your data" feature

That's a hard to implement feature. Hand waving and buzzword lingo will not be enough for people to believe it.

masklinn2• 5 years ago

That's a hard to implement feature.

It really isn't: just remove the data autodetection anything and you're done.

cm21872• 5 years ago

That’s an expensive workaround to just formatting your cells as text before entering the values

dspillett2• 5 years ago

You'd be surprised how much effort can be saved by not relying on human interaction to be reliable.

Though convincing people to switch to a new app for that reason, even if it were free and Free, would still be an uphill struggle.

just formatting your cells as text before entering the values

It is a little more than that. I work with finance people, and there a lot of data is manipulated in Excel but passed around as CSV files for compatibility elsewhere. This causes no end of problems because fixes by setting cell properties are obviously lost in transcription, and date errors creep in as things are moved back & forth between people in the US and those in locales that do dates properly.

prionassembly• 5 years ago

The easier solution is coming up with a 1:1 human-readable-hashing scheme so that MARCH4 translates to "funny-blue-smell-tuesday". Then you keep the safe key in a column next to the unsafe key.

Closi4• 5 years ago

I think the size and scope of Excel is hard to replicate, however it sounds like it would make a great plugin.

masklinn2• 5 years ago

Probably not: people use excel because it's available, it's what they know, and it's what their peers use.

It's been going for more than half a decade now, and genomicists apparently would rather rename genes than stop using excel...

mceoin• 5 years ago

Hi - I'm building a networked spreadsheet product and had heard of this auto-correct problem in genomics research through word of mouth a couple of times, so have asked this question myself. (I was astonished to learn just how much of genomics work involves sending Excel files back and forth!) Here's the conclusion that I have come to:

- It's definitely possible to build a custom spreadsheet product with a small team, even one targeted at such a "niche" user group. So it's an idea worth testing.

- Product can be "backwards compatible" - you can export to xlxs and import from xlxs - so you don't have to change behaviour of the entire industry on day one to get this to work, only a single genomics researcher or lab.

- Pricing, unit economics, etc are unknown to me (I have no background in genomics or scientific research). But presumably you could leverage standard SaaS models and build a viable model up from there using a few case studies. There's definitely schleppy behaviour going on here that can be solved.

- Even a "lifestyle business" has significant upside beyond the financial: improving genomics research improves genomic research!

- The product advantage over time presumably involve building more custom tooling into the genomics / data ecosystem. "Not creating typos" is just the beachhead.

I've never actually interviewed genomics people about their need here, but if anyone knows people with this problem I would love to talk to them: @mceoin on twitter. (DMs open)

tcskeptic• 5 years ago

We need a clippy type feature "It looks like you are working on genomics data, should I turn off all the data destroying auotcorrect features?"

UnFleshedOne• 5 years ago

"It looks like you are working on genomics data, should I direct you to a search engine to find appropriate tooling instead?"

KMnO4• 5 years ago

Heh, sure, if you have $10k+ per user.

(Maybe genomics isn’t as bad as proteomics)

delosrogers2• 5 years ago

This exact problem burned me when I started doing transcriptomics, one of the things I found that helps mitigate the problem is to always keep both the gene symbol and a ID like an Ensemble or Entrez ID for every data point because those don’t get mangled by Excel

guitarbill• 5 years ago

It's easy to hate on Excel or Microsoft, a "dumb" program and a faceless mega corporation. Don't get me wrong, I'm no fan. But the real question is what are journals doing? It seems like the obvious solution is for editors and reviewers to do a quick search for these errors. To me, this is more of an indication of how poor most journals really are.

(You could argue that supplementary data should be in an open format, so you could automate this checking. I'm not against this, either.)

nnmg2• 5 years ago

This is an important point. What have the journals done? Raised their prices and business as usual.

Scientific editors do nothing for data validation. There is no accountability, even after retractions.

Scientific journal editors are glorified gatekeepers for "high impact" work (read: flashy), and then use free reviewer labor to cover themselves so they can call it 'peer reviewed'. In the rare cases when journals do require supporting data, they explicitly ask for excel spreadsheets :(

codetrotter• 5 years ago

That’s the price we pay.

A reasonable alternative would be for example statically typing each column or row in the sheet.

So in one sheet column A contains only floats, col B contains only text, col C contains only dates etc. And in another sheet col A may be date type, col B date type also, col C and D text, and col E monetary amounts.

But this would come at the cost of not being able to mix types in column, and a lot of people want to be able to mix.

And that’s why we’re stuck

zozbot234• 5 years ago

A reasonable alternative would be for example statically typing each column or row in the sheet.

Excel actually supports this out of the box. But it's one more option to set, and people get lazy.

dspillett2• 5 years ago

Also those properties are lost if you transfer the data to another format then back, or don't properly survive copy/cut/paste operations (often cutting will remove everything about a cell including options such as these).

As well as being easy to be lazy and not use the options, it is easy to accidentally undo them also.

eitland4• 5 years ago

Nah, much of this is the same as the dumbness of many autocorrecting keyboards:

automatically "correcting" something that was correct based on a dumb hunch

It is really simple to remove the most embarrassing autocomplete errors on phones:

- just disable autocorrect,

- or preferably use another keyboard that just proposes fixes but leaves it to you to select the suggestion (I use SwiftKey and it does this, others probably exist too).

The same is not possible in Excel as far as I am aware:

You cannot turn of autoformatting.

The whole thing could be easily (on a ux level at least ;-) solved by introducing a single checkbox: "stop embarrassing me" with help text that says "turn off autoformatting".

Checking that box would leave values like you typed or pasted them in.

No need to select on a column basis.

Edit: It is also almost at the level of modern search engines that replace my very specific queries with generic queries about something unrelated or vaguely related.

Edit 2: it is already more than a year ago I guess since iPhones were caught changing the names of correctly spelled medications to the name of another unrelated medication. Sooner or later this incessant drive to be smarter than the user is going to cost lives I'm afraid.

When it comes to search it is already costing me many minutes lost a day, and that is just first order effects, before we get into all the things that doesn't get done because we give up on finding it.

jjk1664• 5 years ago

Right click on top left corner

Select format cells

Select "Text"

This will disable excel's autoformatting. Given the rarity that someone types "SEPT4" into excel and isn't typing a date, putting the option one level deep in a menu seems more appropriate than a top level button.

wtallis• 5 years ago

But this would come at the cost of not being able to mix types in column, and a lot of people want to be able to mix.

I feel like a lot of the desire for this comes from Excel's desire to fill the screen with a single infinite spreadsheet. Apple's Numbers and some other programs make it more natural to have multiple separate tables on screen, each of which can have their own header rows and columns and separate type and format rules for those columns/rows. Excel more or less forces people to emulate this capability by just using a separate region of the same table, which gets in the way of applying consistent formatting and typing rules to whole columns or rows.

Closi4• 5 years ago

A reasonable alternative would be for example statically typing each column or row in the sheet.

You can actually statically type the columns now for imported data via the PowerQuery editor (which is built into Excel), although not a lot of people know how to use it.

PedroBatista• 5 years ago

I generally am very critical of my own gang ( programmers ) for being so into their own bubble they completely lose perspective of the real World, and with that ignorance comes the usual arrogance.

But this time I feel we should give the business to the geneticists. The Excel "problem" is known and has been known for a long time, finance guys and policy makers were maybe the first big ones to "discover" this for slightly different reasons.

Then WHY on Earth these top professionals, elites, "creme de la creme" people continue on this path? And it's not like they love Excel, quite the contrary. Yes, the main reason is they are not programmers so they need to find a way to hack something to solve their problem. The problem with their "problem" solving is they are being slobs in a work that requires the EXACT opposite.

"Oh now we need to hire a programmer too?" - Yes, you do.

"But we don't have the budget for it" - Yes, you do. You see, money is never enough for anything, it's a question of priorities and when you blow millions of dollars with fancy experiments to ruin your career because of Excel, maybe that Python/R/Julia guy was a bargain after all.

I know some of these people, and under the aura of lab coats and distinguished professors it's just some guy/gal trying to crank that paper ASAP to keep the hamster wheel spinning AND they are being sloppy about the data and process AND most of them know it.

Sorry, but no excuses.

breakfastduck8• 5 years ago

Yeah. They use poor/ unsuitable tooling known for unusual data quirks with absolutely no effort to look at alternatives or pay for an expert.

No sympathy from me. Embarrassing. I certainly wouldn't get away with such negligence at my workplace.

Closi4• 5 years ago

The underlying assumption here is that when transferred into R or Julia, with an equivalent level of development effort and testing, that there will be no/fewer bugs. I'm not entirely sure that's true.

I think we are comparing something that is knocked out in an hour in excel to someone spending 4-10x of the time doing it in Python / Julia, but I don't think that's a like-for-like comparison.

breakfastduck8• 5 years ago

So we blame the software instead of blaming the downright lazy people who can't be fucked learning an appropriate tool even though that's their entire career.

Honestly these kind of things really wind me up.

ironmagma5• 5 years ago

If a problem is re-occurring and between lots of people, it's probably the fault of the system that powers it. The system in this case is the software, so blaming the software is appropriate.

breakfastduck8• 5 years ago

No it isn't. This isn't a gotcha. This is probably one of the most well known software behaviors on the planet.

You KNOW the problem exists, yet you STILL choose to use the software, THEN expect it to change its behaviors because its inconvenient for your very specific use case. Microsoft aren't going to fix this behavior because there are likely millions if not billions of spreadsheets that rely on it.

That is not a software problem.

Closi4• 5 years ago

So we blame the software instead of blaming the downright lazy people who can't be fucked learning an appropriate tool even though that's their entire career.

Further, the software would be fine if you use it properly for this sort of use case (i.e. bring the data in via PowerQuery which has enforced types).

psychometry• 5 years ago

compbio is not a field you'd want to go in if you want to use well-written tools exclusively. There's tons of absolutely abominable Perl and R code powering critical pipelines everywhere you look.

What makes matters worse is that scientists, who are already not great coders, have to work with even less technical people (i.e. the physicians). These collaborative processes will inevitably involve software like Excel. Need your M.D. co-collaborator to annotate a gene list or whatever? Your CSV's getting re-saved in Excel whether you like it or not.

IAmEveryone• 5 years ago

There is one problem people have for their use case, so it’s inappropriate? Have you ever created an issue on some GitHub project? I guess you were using the wrong tool?

People choose their tools for some reason(s), and there is no law of man or nature that says that using a common tool for some esoteric purpose is somehow wrong, just because you consider it too easy.

breakfastduck8• 5 years ago

That does not apply to the windows office suite, literally built on megatonnes of legacy & bug level compatibility. Or windows for that matter.

BugsJustFindMe2• 5 years ago

In reality the appropriate tool for research data actually doesn't exist (make it and become very rich). Excel is only sort of close if you squint.

HPsquared• 5 years ago

They might move to a relational database, but then the gene named '; DROP TABLE genes; will start causing problems.

nnmg2• 5 years ago

Excel is used as a database/storage/interchange format, especially after the initial analysis by someone who uses python or R. Bioinformatician does the analysis, then the PI wants to see it so they can Ctrl-F for genes they are interested in, so out comes an excel document.

And really, even if you know python or R, are you really going to fire up a jupyter notebook, load the data, and run pandas queries every time someone in lab meeting or after a talk asks you about this gene or that gene in your data?

I think the important question is why is date conversion a default? Would it really break backwards compatibility for MS Excel users if date conversions were explicit instead of automatic? Turning that off by default would fix a lot of this.

dec0dedab0de• 5 years ago

I don't do any scientific research, but I have been using jupyter as a replacement for excel since it was called the ipython notebook. I don't really use pandas all that often, I just find it easier to read and edit data in python. Though I first learned ipython added the notebook from a talk Wes McKinney gave about Pandas.

SonicScrub2• 5 years ago

I don't work in bioinformatics, but what you are describing is a completely accurate description of what I experienced working in manufacturing quality control. Raw data came in from suppliers in the form of spreadsheets, and management wanted to see results in spreadsheets. Meaning all our quality data was subjected to these issues. The date formatting issue was a particularly annoying "gotcha", particularly when features were defined with a XX-XX numeric code. The number of times I had to deal with someone in a meeting saying "hey, why is this feature called October-13?!" Super frustrating.

If I could choose the tools used by the whole process involving multiple different companies and departments, hey I would! It would be python all the way down. But I was but a cog in a massive organization.

dragonwriter4• 5 years ago

A lot of "safety culture" is composed of things like checklists and hazard warnings which are more geared towards shifting the blame for accidents onto somebody else than actually preventing those accidents,

If you stay in spreadsheets these problems mostly don’t occur (that is, once data entry is squared away so that the initial spreadsheet has what you want it doesn't tend to get lost), its when you move in and out of spreadsheets via text and take the path of least resistance [0] to do the transition that the problem occurs.

[0] and to be fair, there is a lot of resistance off that path.

BugsJustFindMe2• 5 years ago

Excel is used as a database/storage/interchange format, especially after the initial analysis by someone who uses python or R. Bioinformatician does the analysis

Sometimes, but the situation is in reality worse than that. Excel is also used as the gold standard database/storage/interchange format of record for random shit that clinical researchers have typed in by hand whether directly or transcribed from other notes, often when that data isn't actually fundamentally tabular in nature because people really like working in grids. Even when grids hurt more than help.

A big secret in genetic research is that the MDs, grad students, project managers, and coordinators running the research programs are often not super focused on what well-structured data looks like and don't know what things like "key-value store" or "nested tree-like structure" mean, and even if they did there aren't good GUI tools for entering them anyway, and it leads to countless errors that maybe (here I speculate) they just assume will wash out as noise.

I think the important question is why is date conversion a default?

Yes, why any kind of conversion is ever the default is a real money question.

quantified3• 5 years ago

For the finance and business office worker, it seems to have traction. Just like auto-creating an emoji when you type a : character. Excel is for offices, not specializations of scientists. Bummer.

tssva• 5 years ago

This isn't a Microsoft or Excel issue. It is a choosing a spreadsheet issue. LibreOffice and Google Sheets for instance by default also try to determine what type of data is being entered.

The issue is choosing the incorrect tool or if you have no choice but to use a spreadsheet failing to learn how your tools work.

cyanydeez2• 5 years ago

unfortunately, the reality is backwards compatibility with everyone who came before you, sets your budgets and expects to review data with minimal upstart.

people like spreadsheets because it provides very immediate feedback

koksik202• 5 years ago

I remember I was helping my wife with her Uni assignment data split between excel and matlab couldn’t believe the errors we got from misinterpretation looks like lab science needs to catch-up with IT standards for handling data with correct integrity

Tagbert• 5 years ago

If an excel spreadsheet is setup properly, this conversion doesn't happen. If the cells are designated as "text", then conversion doesn't happen.

This is definitely a problem if you are just opening a raw CSV file and editing it as the default cell type does allow autoconversion.

djbebs• 5 years ago

Except from personal experience I can tell you that even when you go out of your way to set the correct data types, if you try to copy paste any data into those cells excel will ignore everything you set and try to automatically change the data types.

If you know how to get it to never do that sort of thing, please let me know, because this is causing major issues in production software...

Autocorrect errors in Excel still creating genomics headache

Related Stories