April 04, 2011 | By: Nick Morris

Aa Aa Aa

Exam Question: Spreadsheets are evil. Discuss.

Wouldn't that make a great exam question? "Spreadsheets are evil. Discuss." What would your answer be? My answer would be that spreadsheets are evil 95% of the time (and I have a spreadsheet that proves that - just kidding).

The birth of the spreadsheet

The first spreadsheet program was called VisiCal, it ran on an Apple II and was invented by Dan Bricklin and Bob Frankston back in the late 1980s (for more information see Wikipedia: Spreadsheets and The First Spreadsheet - VisiCalc - Dan Bricklin and Bob Frankston).

Now, a big clue as to what the program was invented for is in the name - VisiCal - that is, it was invented for doing calculations. And that is the key - calculations - it was not invented to be used as a database (see Do you speak my language: D is for Database, debug, DNS). The number of times I come across students, scientists, lecturers and administrators using a spreadsheet when they should be using a database is depressingly high.

Why are spreadsheets evil?

Spreadsheets are not really evil. It is the producers of spreadsheets that are evil!

If a spreadsheet is used correctly it can be a wonderful tool as it can speed up work, remove the need for tedious calculations, and increase efficiency. However, most of the spreadsheets I see, and get sent to me, are not used correctly. And it is this incorrect use that makes them evil!

How to know if your spreadsheet is evil?

It is quite easy to spot an evil spreadsheet, all you have to do is look at it.

If the spreadsheet looks like figure 1, then it is evil. However, if it looks like figure 2, then it is not evil.

The difference between the spreadsheets shown in figures 1 and 2 is that the spreadsheet in figure 1 contains no calculations, and this is what makes it evil as spreadsheets were not designed to do what is shown in figure 1, they were designed to do calculations, as can be seen in figure 2.

If you find yourself constructing a spreadsheet that looks like figure 1 then please STOP. What you need is a database. Spreadsheets were not designed to do what you are trying to do!

What makes 'non-calculation' spreadsheets evil?

One of the biggest problems with using spreadsheets to store 'non-calculation' data is that a number of spreadsheet programs have an auto-correct feature, which will happily change your data for you to what the program thinks is correct. I have seen this happen numerous times.

Look at figure 1 again. As can be seen the program has changed some of the student number (which should all have 7 digits) to a 'correct' number, that is, in the case of student number starting with a zero the leading digit has been removed. In one case the number has been changed to a scientific notation. Also, the date column. There is no indication whether the date is mm/dd/yyyy or dd/mm/yyyy, hence the program may change 01/10/2011 to either the 1st October 2011, or the 10th January 2011.

I can assure you I am not making the above up as in the last 2 months I have received spreadsheets containing the sorts of errors and problems seen in figure 1. I have also, over the years, encountered problems where students have stored data in spreadsheets and then had the program change all there gene names/tracking codes etc. to a 'correct' value. This has resulted in months of work lost and confused.

What about the 5% that are good?

I use spreadsheets in my teaching for giving feedback, particularly in lab classes that involve complex calculations. I put the student results in to the spreadsheet, and the spreadsheet does the marking, and gives the feedback. This works great. And the key here, is the spreadsheet is being used for what it was designed - calculations!

What can I use a instead of a spreadsheets?

This is a tough one as there aren't that many good (easy) software packages out there. I personally like Bento for the Mac, iPad and iPhone. It is easy to use, and ironically has a spreadsheet like interface for data entry, and syncs between a Mac, an iPhone and an iPad. A disadvantage about the program is that there is no Windows version. (There will be more on Bento in a later post.)

A database program that works on the Mac and Windows is FileMaker Pro. I used to use FileMaker Pro a lot between 1992 and 2005, in fact, I ran my lab on it. We used it for tracking everything from orders, health and safety, experiments, and where things were in the freezer. However, I stopped using FileMaker Pro as it became too expensive and in the mid-2000s I wanted to move away from using a static computer based system to a webbased interface for my work, and FileMaker Pro at the time couldn't offer that.

(Disclaimer: I do not work for the company that sells and/or produces the above program, and I do not get a 'kick-back' from the company for writing this article. I do, however, routinely use the program in my teaching, I do find it extremely useful, and so I thought I would share my experience.)

Summary

Spreadsheets are good. They can be used for good and great things, but they are just a calculator and are not a replacement for a database. Please think about for what you are using them!

6 Comments

April 10, 2011 | 12:32 PM

Posted By: Nick Morris

Carl - your last comment on "It's much easier to email a spreadsheet than a most databases." got me thinking, the answer may be sqlite (http://www.sqlite.org/).

On the Mac software can be written to use 'core-data' and to produce sqlite files. These files are 'database files' and can be as easily shared as you would a spreadsheet. However, I am not aware of any 'consumer' software that uses them in such a way. Bento (mentioned in the post) uses sqlite, but can't export in that format. I am not sure about FileMaker Pro.

April 09, 2011 | 08:58 AM

Posted By: Nick Morris

Carl, thanks for the link to "PowerPoint Is Evil" (http://www.wired.com/wired/archive/11.09/ppt2.html) - I hadn't seen that before and it should be a compulsory read for all students and lecturers.

One interesting point that really resonated with me was "In a business setting, a PowerPoint slide typically shows 40 words, which is about eight seconds' worth of silent reading material. With so little information per slide, many, many slides are needed." Basically, the student would be better spending their time in the library rather than the lecture theatre!

April 08, 2011 | 04:23 PM

Posted By: Carl Setzer

I'm reminded of Tufte's seminal piece, "PowerPoint Is Evil" (http://www.wired.com/wired/archive/11.09/ppt2.html). I think the reason so many are tempted by database/spreadsheets is that they're more portable. It's much easier to email a spreadsheet than a most databases.

Besides databases, I've seen spreadsheets used for reports that should be done in a word processor. If there are no calculations, no need for the spreadsheet.

April 06, 2011 | 08:09 AM

Posted By: Nick Morris

Ah, yes, PowerPoint as Photoshop. I have also seen that going on. Another classic misuse.

April 05, 2011 | 08:22 PM

Posted By: Alessandro Ferrari

I've also seen people using PowerPoint as Photoshop...wasting so much time!

April 05, 2011 | 04:52 PM

Posted By: Nick Morris

Thanks to @sjcockell on Twitter for flagging this paper today:

"Mistaken Identifiers: Gene name errors can be introduced inadvertently when using Excel in bioinformatics" by: Barry Zeeberg, Joseph Riss, David Kane, Kimberly Bussey, Edward Uchio, W. Marston Linehan, J. Carl Barrett, John Weinstein BMC Bioinformatics, Vol. 5, No. 1. (23 June 2004), 80. doi:10.1186/1471-2105-5-80

Kind of fits in neatly with the post above!

Exam Question: Spreadsheets are evil. Discuss.

share

Email your Friend