A seemingly
very long time ago I supervised a diploma student in Freiburg and my old blog
was for a while full of complaints about him and his endless inabilities to use
his brain, common sense and judgment. I will never forget how he blankly
refused to understand even the most basic of statistics (the mean of 3
repetitions), and how he finally made things only worse for himself by
insisting he’d do all the stats in excel. I and many others have said this
before: excel is not a statistics programme. Especially if you don’t know what
you’re doing, don’t do it in excel.
Anyway, before
I get carried away again: he got his diploma, I got a paper, end of story.
Until Ian
started putting together a workshop on how not to use excel, and asked for
examples of particularly bad excel spreadsheets. Then I knew I was sitting on a
goldmine.
Ian send
through a paper that has a few golden rules for creating spreadsheets that you
will still be able to use after excel has updated and rendered all old versions
unreadable, after you haven’t looked at your data for 20 years and forgotten
all about it, or when you give it to someone else to work on. It’s a good
resource so go have a look at it if you use excel, or any data entry software,
and/or data processing software/programs.
Things that
my colleagues and I came up with that are extremely annoying in bad
spreadsheets:
- Colour coding 1) it hurts the eyes, 2) it isn’t informative when you have the right column/row descriptions 3) it is lost when imported into other software
- More than 1 row of header, or unevenly distributed (the first row has some info, the second some more, the third is left blank for fun, the fourth has sporadic info on units used, on the fifth an explanation of the project …) Again: it really badly imports into the important statistics software.
- Non-continuous columns, e.g. first 20 rows of data, then a different header-row and different data the next 27 rows, then another header and data from an entirely different project, etc.
- Graphs over the data.
- Functions that are hard to follow (backtracking to several worksheets, several coumns/rows, incomprehensible formulae)
I think my
student violated practically all those rules, which made it living hell to get
it all loaded up in an R readable format or to get back to troublesome data,
which were impossible to find.
But: the
good news: I won the prize for worst spreadsheet of the workshop! Some nice
chocolate. I briefly considered sending it to the student (who is now messing
up performing forest surveys and with whom I kept out of contact for fear he
would one day stand on my doorstep in NZ, his dream being to buy a car and
drive around here for a year). But I ate it all myself.
