What They Forgot to Teach You
Issue #4,
đź‘‹ Welcome
I love a good spreadsheet. My teenage daughter knows it and she is using it against me.
Her campaign for a puppy has a large spreadsheet component. She has built a Google sheet listing every single thing we will apparently need (crate, bed, vet registration, food, insurance). Each item has a URL link to where we can buy it and a cost. The items are colour-coded into what we pay up front versus what we pay forever. The second tab is a list of breeders with links. She slid the laptop across the table and said, “You have all the information you need, Mum.”
And honestly? I was proud. It is a beautiful spreadsheet. It is thorough and more organised than most of the data files I was handed as a supervisor.
There is no doubt that a well organised spreadsheet can be persuasive, but there are things about Excel that make it dangerous for young researchers (and old researchers really). I have been prepping for lab meeting this week and reminding myself of the really famous data horror stories that were caused by spreadsheet misuse. Like the fund that lost 92 million dollars because someone made a date error. The 16,000 Covid cases that vanished because the spreadsheet ran out of rows. A famous economics paper, used to justify austerity around the world, that was undone when a student who was doing a reproducibility project found that a key formula had left five countries out of an average.
There are hundreds of stories documented by the European Spreadsheet Risks Interest Group, all cases of avoidable embarrassment. Join us at lab meeting (FREE ONLINE) this week to chat about how to set up your data sheet in a way that saves you time (and potential shame) down the line. Link to RSVP below.
đź“– This week I read
Gene name errors are widespread in the scientific literature — Ziemann, Eren & El-Osta (2016), Genome Biology. Read it here →​
This is probably the most well-known Excel horror story. Researchers in genetics have long been battling the default Excel settings, which quietly “help” you by converting things it thinks are dates. So the gene symbol SEPT2 becomes “2-Sep”. MARCH1 becomes “1-Mar”.
When these researchers scanned the supplementary data files of leading genomics journals, they found that roughly one in five papers had gene names mangled this way. The problem was so entrenched that the body in charge of naming genes eventually renamed some of them just to stop Excel from messing with them. And years later, it was still happening.
Interestingly… Google Sheets does not assume these values are dates.
🎓 The hidden curriculum
How did you learn to use a spreadsheet?
It was probably not in a research methods class. Most of us are self-taught, picking up Excel for a household budget, a trip itinerary or (in my house, this month) a campaign for a puppy. In one survey of researchers, not a single one had any formal spreadsheet training, about two-thirds had taught themselves, and yet the majority rated their skill level as “intermediate” or better.
This is a great example of the hidden curriculum. Nobody ever sat you down and taught you Excel. You absorbed it, one task at a time, and familiarity slowly turned into confidence. But here is the trouble with teaching yourself: you learn how to make the thing work, you never learn the ways it quietly goes wrong. Being comfortable with a tool is not the same as understanding how it can trip you up, particularly when you are applying your puppy-campaign spreadsheet skills to a real research project.
The good news is that other people have done the work of distilling best practice for you. Broman and Woo’s Data Organization in Spreadsheets is a short, readable place to start.
Come and work through it with us at Wednesday’s lab meeting (FREE ONLINE, link below), and we’ll set your data sheet up so it saves you time instead of costing you sleep.
💡 What I’m into
Remember those 16,000 Covid cases I mentioned up top? There is a whole podcast episode about that one, and it is excellent. Cautionary Tales with Tim Harford has an episode called “Wrong Tools Cost Lives” about the point in the pandemic when the UK’s case-tracking system quietly lost thousands of positive results, because the data was being held in an old version of Excel that simply ran out of room and started dropping rows.
It is a very good listen, and a slightly terrifying reminder that the humble spreadsheet has limits that nobody tells you about. Find it here →​
đź“… Join me
Free live lab meetings on Zoom — RSVP using this Calendly link​
- The danger of spreadsheets — Wednesday July 29, 12pm–1pm AEST. Everything above, but live. A few simple habits that catch errors before your marker does.
- Open Science + Good Writing — Wednesday August 5, 12pm–1pm AEST. Why some sentences stick and others slide straight off the page, and how to write a thesis your marker actually remembers.
See other upcoming lab meeting topics at www.revealresearch.org/events​
Small group coaching — N = 10, mini-masterclass $49 per person
- ​Experimental Design Clinic — Thursday August 7, 12–2pm AEST. Let's review the decisions you make before you collect a single data point because they are the ones you can't really fix later.
- ​Writing your general discussion — Thursday August 21, 12–2pm AEST. The discussion is where most theses run out of steam. It doesn’t have to be the section you dread.
đź‘‹ thanks!!
The puppy campaign continues. The spreadsheet has now grown a third tab that is linked to a Google Form which is collecting data about potential names for the dog. I suspect I am going to lose this one, but at least the data will be beautifully organised.
Until next time, Jen
P.S. If you have a friend who is knee-deep in their thesis (and their data), forward this and suggest they subscribe to “What They Forgot To Teach You”. It’s free, and it might be exactly what they need.
P.P.S. If you missed the first three issues, they’re almost certainly hiding in your spam folder, having a lovely time. Go rescue them.
I'm Jen, a developmental psychologist and former UNSW A/Prof. I help new researchers go from feeling like they've missed something everyone else knows, to feeling like they genuinely belong in research, so they can get through their research degree and into the career they actually want.
|
​