Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Autocorrect errors in Excel still creating genomics headache

nature.com

Post reply on HN
Print view View original

61–70 of 108 posts

Re: Autocorrect errors in Excel still creating genomics headache

#61

Post by breakfastduck »

ironmagma wrote: post #55
breakfastduck wrote: post #50

Earlier quoted context omitted.

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…

People don't choose to use Microsoft products, the software is chosen for them by their organization. For a problem like this, the existence of the article may very well be the first step to switching to another solution. The first step in that process is raising awareness that there is a problem.

A convenient cop out there.

"I didn't install it, I only chose to use it for something it isnt suited for instead of looking for alternatives, not my fault"

Re: Autocorrect errors in Excel still creating genomics headache

#62

Post by qayxc »

pletnes wrote: post #34
qayxc wrote: post #24

Earlier quoted context omitted.

> Use the right tool. Step one should be learn your tool, no matter the tool. There's several ways to avoid the issue in Excel. If someone working with Excel isn't able to learn that (heck, a simple template would suffice), I have no hope the same demographic would have any success with R or Python.

As someone who has programmed in python for a decade, and no idea how to do anything right in Excel, I would beg to differ.

Do you use Excel on a daily basis for productive work as well?

If not then your personal experience is irrelevant in this context.

Re: Autocorrect errors in Excel still creating genomics headache

#63

Post by nnmg »

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.

Re: Autocorrect errors in Excel still creating genomics headache

#64

Post by eitland »

jjk166 wrote: post #42
eitland wrote: post #30

Earlier quoted context omitted.

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 prob…

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.

> 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.

Given the rarity that someone has pasted 100 000 rows into and want them to be autoborked disabling autoborking, at least for pasted data seems appropriate enough for me ;-)

Re: Autocorrect errors in Excel still creating genomics headache

#65

Post by ironmagma »

breakfastduck wrote: post #61
ironmagma wrote: post #55

Earlier quoted context omitted.

People don't choose to use Microsoft products, the software is chosen for them by their organization. For a problem like this, the existence of the article may very well be the first step to switching to another solution. The first step in that process is raising awareness that there is a problem.

A convenient cop out there. "I didn't install it, I only chose to use it for something it isnt suited for instead of looking for alternatives, not my fault"

People working in large organizations are frequently not even allowed to install software that isn’t pre-approved by N layers of management. The bottom line though is that Excel is made for data entry. If it’s not suitable for that use case, that’s a big problem with the product-market fit.

Re: Autocorrect errors in Excel still creating genomics headache

#66

Post by nnmg »

guitarbill wrote: post #26

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,…

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 :(

Re: Autocorrect errors in Excel still creating genomics headache

#67

Post by da_chicken »

dragonwriter wrote: post #60
da_chicken wrote: post #56

Earlier quoted context omitted.

> There's several ways to avoid the issue in Excel. No, not really. There are several ways to help reduce the issue, but none of them eliminate what it does. Example that comes to mind is the data file for College Board's SAT test. The data formats for student reports for schools come in two formats: PDF (one page per student), CSV, and fixed-width. That is the comprehensive list of your options. College Board doesn'…

> It doesn't matter how you format the CSV. Excel will do the above. Only if you open the file, so that Exel assumes defaults for formatting. If you import the CSV (Data -> From Text in the current UI) you can specify the format with the Import Text Wizard as described on the College Board instructions for using the file. Except... Unfortunately, the College Board has outdated instructions on their website; Excel use…

Yeah, that's also obnoxious. The Data From Text wizard lets you easily import the data... but if you do that then the first row no longer defaults to headers. So if you open it that way and want to filter or sort the data, your headers will disappear. You have to go into full blown Power Query to do both prevent reformatting and use column headers, and you have to reassign the data types on a much more complex interface.

We have now changed a 1 second automated process that anybody could do, into a 2 minute manual process that requires knowledge of data types. We've gone from a non-technical task to a highly technical task. To open a file without completely munging the data.

It also will sometimes do weird things like exclusive lock the file on disk until you close every open window of Excel because it creates data connections to the file.

Re: Autocorrect errors in Excel still creating genomics headache

#68

Post by jjk166 »

eitland wrote: post #64
jjk166 wrote: post #42

Earlier quoted context omitted.

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.

> 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. Given the rarity that someone has pasted 100 000 rows into and want them to be autoborked disabling autoborking, at least for pasted data seems appropriate enough for me ;-)

I would expect dates to show up in large datasets pasted into excel orders of magnitude more often than genes. For the people who use excel, autoformatting is overwhelmingly the desired default behavior.

Re: Autocorrect errors in Excel still creating genomics headache

#69

Post by dragonwriter »

da_chicken wrote: post #67
dragonwriter wrote: post #60

Earlier quoted context omitted.

> It doesn't matter how you format the CSV. Excel will do the above. Only if you open the file, so that Exel assumes defaults for formatting. If you import the CSV (Data -> From Text in the current UI) you can specify the format with the Import Text Wizard as described on the College Board instructions for using the file. Except... Unfortunately, the College Board has outdated instructions on their website; Excel use…

Yeah, that's also obnoxious. The Data From Text wizard lets you easily import the data... but if you do that then the first row no longer defaults to headers. So if you open it that way and want to filter or sort the data, your headers will disappear. You have to go into full blown Power Query to do both prevent reformatting and use column headers, and you have to reassign the data types on a much more complex interf…

> The Data From Text wizard lets you easily import the data... but if you do that then the first row no longer defaults to headers. So if you open it that way and want to filter or sort the data, your headers will disappear. You have to go into full blown Power Query to do both prevent reformatting and use column headers

You don't, because while the wizard doesn't default to headers, the first screen of the wizard has a checkbox for it, so the option is there without Power Query.

Re: Autocorrect errors in Excel still creating genomics headache

#70

Post by psychometry »

breakfastduck wrote: post #45

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.

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.

Post reply on HN