6 ms·

Another comment asks, why are we seeing so many articles about Excel? The main reason is that Excel was recently in the news because a paper about economic po

Another comment asks, why are we seeing so many articles about Excel? The main reason is that Excel was recently in the news because a paper about economic policies for countries was found to have been based on data kept in an Excel spreadsheet that was poorly documented enough that errors in the data weren't found right away. (This is the highly condensed version of the story.) These stories resonate here on HN because many, many, many of us have had occasion to use Excel as a tool. Members of the general public who share information with me (for example, contact lists for youth soccer teams) have learned in a corporate environment to treat Excel as the "universal" data exchange format. So I will be dealing with Excel spreadsheets for years even if I never create another one. One expects Excel to operate like a tool, a way to manipulate data in some straightforward, well defined ways. I don't expect Excel to do what is described in the article submitted here: treat any text data value with certain embedded strings as special data types that the program can rewrite without explicit user command. That turns Excel from being a tool in the workplace to being a surly co-worker who habitually messes up other workers' projects. I intentionally minimize my use of Excel because I don't like its artificial intelligence turning into artificial stupidity while I try to get my work done. To find out that Excel is actually actively impeding medical research by rewriting data cells in spreadsheets is a dismaying example of why I can't treat Excel as a useful tool, so of course I was glad to upvote this informative submission. AFTER EDIT: I shared the link that opened the thread here among my Facebook friends, and one friend commented, "This is (luckily) old news and no bioinformatician worth his keyboard uses Excel any more. It's just too much of a wild card." He followed up after another friend's comment with "Microsoft is squarely in the wrong here. The tool aggressively reformats highly technical data fields and the behavior is remarkably hard to keep turned off. I've been working in this specific field for 15 years, and I can guarantee that the power users do know their tools. What they know these days is to go use R or even one of the OSS applications like LibreCalc. Unfortunately, more naive users new to bioinformatics analysis routinely get tripped up by this and other overly assistive features of the Office suite."

maxerickson 14y ago
Excel is not particularly rewriting data cells in spreadsheets. When importing other formats, it has some automatic conversions that people apparently find convenient. It's a little bit pedantic, but a text file with some sort of structured format is not the same thing as a spreadsheet. It probably doesn't make any sense to apply automatic conversions to formats that are more or less defined by convention, but if you mark a column in an Excel file as text, Excel won't apply magic to that column. The bigger problem is that it is considered acceptable to not keep a record of the changes being made to the data (a script can serve nicely as both a data processor and a record of the processing being done).
- ams6110 14y ago
  I am surprised that this is a surprise. Anyone who has used Excel to open a CSV file of any complexity has surely encountered incorrect, automatic "interpretations" of values as dates or numbers.
  - Pxtl 14y ago
    Yeah, that. Excel's CSV behavior is worse-than-useless, which is a shame since CSV is generally treated as the easiest way to get data into Excel.
  - pbhjpbhj 14y ago
    Why wouldn't an import tool have an option "make automated conversions" or the ability to _simply_ turn automated conversions off with clear state feedback (eg as a checkable button like for overwrite/insert modes¹). Surely MS research has shown that this is one of the great pain points with Excel/Word? [¹ I haven't used MS Word for real in over a decade, it used to have a display of OVR in a bottom panel IIRC for when overwrite mode was on.]
    - lutusp 14y ago
      > Surely MS research has shown that this is one of the great pain points with Excel/Word? Perhaps, but Microsoft is infamous for ignoring Excel's many faults. An article linked yesterday described a litany of very serious statistical errors of long standing, without any indication of a timetable to repair the defects.
- lutusp 14y ago
  > if you mark a column in an Excel file as text, Excel won't apply magic to that column. The problem is that the CSV file format has no reliable mechanism to mark a column as text( * ). Known workarounds include inserting a single quote at the beginning of a field, but this single quote may remain in the field forever, polluting the data downstream from the import. * The obvious approach of enclosing CSV text fields in quotes, and non-text fields without quotes, won't work -- too many Excel versions strip out all the quotes while importing, without considering the implication of their selective application. Also, in many Excel versions "DEC1" is converted into a date whether or not it's quoted.
niggler 14y ago
"Another comment asks, why are we seeing so many articles about Excel?" There's a link next to posts. It's easy to reply to the person asking the question by clicking the link and typing a reply.

maxerickson 14y ago

Excel is not particularly rewriting data cells in spreadsheets. When importing other formats, it has some automatic conversions that people apparently find convenient. It's a little bit pedantic, but a text file with some sort of structured format is not the same thing as a spreadsheet. It probably doesn't make any sense to apply automatic conversions to formats that are more or less defined by convention, but if you mark a column in an Excel file as text, Excel won't apply magic to that column. The bigger problem is that it is considered acceptable to not keep a record of the changes being made to the data (a script can serve nicely as both a data processor and a record of the processing being done).

ams6110 14y ago
I am surprised that this is a surprise. Anyone who has used Excel to open a CSV file of any complexity has surely encountered incorrect, automatic "interpretations" of values as dates or numbers.
- Pxtl 14y ago
  Yeah, that. Excel's CSV behavior is worse-than-useless, which is a shame since CSV is generally treated as the easiest way to get data into Excel.
- pbhjpbhj 14y ago
  Why wouldn't an import tool have an option "make automated conversions" or the ability to _simply_ turn automated conversions off with clear state feedback (eg as a checkable button like for overwrite/insert modes¹). Surely MS research has shown that this is one of the great pain points with Excel/Word? [¹ I haven't used MS Word for real in over a decade, it used to have a display of OVR in a bottom panel IIRC for when overwrite mode was on.]
  - lutusp 14y ago
    > Surely MS research has shown that this is one of the great pain points with Excel/Word? Perhaps, but Microsoft is infamous for ignoring Excel's many faults. An article linked yesterday described a litany of very serious statistical errors of long standing, without any indication of a timetable to repair the defects.
lutusp 14y ago
> if you mark a column in an Excel file as text, Excel won't apply magic to that column. The problem is that the CSV file format has no reliable mechanism to mark a column as text( * ). Known workarounds include inserting a single quote at the beginning of a field, but this single quote may remain in the field forever, polluting the data downstream from the import. * The obvious approach of enclosing CSV text fields in quotes, and non-text fields without quotes, won't work -- too many Excel versions strip out all the quotes while importing, without considering the implication of their selective application. Also, in many Excel versions "DEC1" is converted into a date whether or not it's quoted.

niggler 14y ago

"Another comment asks, why are we seeing so many articles about Excel?" There's a link next to posts. It's easy to reply to the person asking the question by clicking the link and typing a reply.