Excel -- wrong tool for the job?

"Errors are introduced because some genetic identifiers look very like dates to Excel. If the spreadsheet is not properly set up, it will convert an identifier, such as SEPT2 to a date: 2-Sep. The conversion, the researchers say, is irreversible: once the error has been introduced, the original data is gone."

http://www.theregister.co.uk/2004/07/16/excel_vanishing_dna/

What do you folks think?

(I know Joel says he still uses Excel spreadsheets for a wide range of tasks.)

Joe
http://www.joegrossberg.com

Joe Grossberg
Monday, July 19, 2004

Store data in Access

Analyze it in Excel.

Stephen Jones
Monday, July 19, 2004

Yes, I've come across a few problems like that. For example importing a catalog of items with titles item numbers "00062", Excel will drop the leading zeroes unless you tell it not to.

BUT it does give you the option to tell it not to. AND unless you're not looking at what you're doing, you know that SEPT2 just converted into a date & you go into the FORMAT option and tell it to treat that cell as text. Heck, highlight all the cells and tell it to treat them as text if that's what you want.

www.MarkTAW.com
Monday, July 19, 2004

I have memories of exporting a contact database to Excel before importing it into the replacement database (Excel was the only program we had capable of importing CSV and outputting DBF). Years afterwards the database still contained phone numbers of the form 1.23456E+12.

A.T.
Monday, July 19, 2004

The real question is, why is this news?

"Hello, we highly academic and smarter-than-you scientists have been screwing up our formatting in Excel even though the options for correctly formatting our data are there.  As a result, we've corrupted a lot of our own data and now we'd like to report it to the media."

err?

muppet
Monday, July 19, 2004

I'm with muppet on this one.

If someone screws their data up in Excel, because they don't know how to use it, what can you say.

Fair enough, if it was a bug in Excel, then complain, but if you just don't know how to use the thing, then don't blame the product.

As an aside, I'm convinced that this one of the reasons that Access gets such a bad press. People use it inappropriately and something goes wrong. Hmm, how shocking. End of aside.

Steve Jones (UK)
Monday, July 19, 2004

Excel is the wrong tool in that it has numerous "features" designed to make it universal.  There are numerous packages available that will import data without converting into other weird formats, etc.  If it was THAT bad, they'd just hire some people to get that going, right?

I would!

sir_flexalot
Monday, July 19, 2004

Excel is one of the few good tools for moving tabular extracts around
the modern office.

It a regular part of my Job to extract or summarise a couple of
hundred rows out of our database warehouse.

Most of the users simple don't know Access and it not suitable for the
tasks that they want to anyway. However I have been bit by address
with number-fields such as "3-6" being coverted into dates. I have now
learnt to always select the entire sheet and format the cells to text
before pasting anything directly in Excel now, and covert it the
correct type after the fact.

ITECS
Monday, July 19, 2004

I fail to see how the change would be irreversible. Switching SEPT03 to 9-2003 or whatever can be easily reversed.

MilesArcher
Monday, July 19, 2004

But was it SEP03 or SEPT03 that got switched?  It seems to me that the "right" solution to this one would be to just store the original data with the formatted data.  If you then find out that your formatting is wrong ("Whoops, forgot to set that column to not format input as dates"), you can use some sort of reformat command to parse the original input again.

Kevin
Monday, July 19, 2004

"If someone screws their data up in Excel, because they don't know how to use it, what can you say."

You can say there is a usability problem with Excel.

It's real easy to write it off as "Stupid users. They should have known better."

But if freaking geneticists aren't smart enough to find all the "features" needing reconfiguration, can you reasonably maintain that it's the responsibility of the user to know every potential pitfall with using Excel "out of the box"?

Joe
http://www.joegrossberg.com

Joe Grossberg
Monday, July 19, 2004

More recent versions of excel put a little 'post it' next to conversions so that you can reverse them if you want.  A very neat feature.  The Excel team seem to get things right most of the time.

Ged Byrne
Monday, July 19, 2004

I had to write the mother of all data gathering report/apps using excel. It was a nightmare. 5 Mb in size, emailed between sites/jobs and myself, where I uploaded the data into SQL Server. Felt like cutting my own throat each time a user modifed the file causing my uploader to crash, but those above wouldn't let me write a real program to do the same thing.

Excel is good in small doses.

Jack of all
Monday, July 19, 2004

muppet, this is news because those smarter-than-though scientists weren't aware that it was the wrong tool for the job. And in fact, most people aren't aware that even though Excel is an excellent tool for small jobs, it's the wrong tool for most serious jobs due to being smarter than you about what you want to do.

Ori Berger
Tuesday, July 20, 2004

ok, so Ori what you're saying is that its news, reported by scientists, that the scientists (those same reporting scientists) are incompetent.

Again, this is news because...

muppet
Tuesday, July 20, 2004

Excel is the wrong tool to store data in.

I lost a whole set of marks because when you change the sort by criterion and have hidden columns,  under some circumstances (hidden columns to the left I think) the hidden columns don't resort.

Keep the data in Access, and have a form with a big button and the Excel logo on it so they can just hit it and export all the data to Excel.

Stephen Jones
Tuesday, July 20, 2004

  Recent Topics

  Fog Creek Home