Over the last couple of months I have seen a lot of scientists’ Excel spreadsheets. Approximately 150 spreadsheets, to be exact. All of this important information will feed into the requirements that I send off to the developers for the DCXL add-in, but in the mean time I am collating some very interesting information about how folks use Excel, for better or for worse.
It’s the defining moment in the interview: the sheepish opening of the spreadsheet. Scientists are often embarrassed by their data management, and tend to make a lot of excuses about why their spreadsheets look the way that they do. What’s interesting is the people that are most worried about their current system are often better off in comparison to others. At the end of the day, it’s about whether your system works for you and your needs.
There is one theme that seems to surface over the course of these interviews: scientists who were “handed down” spreadsheets for a project are often better off than those that start from scratch. This certainly makes sense: it’s likely that someone has perfected the spreadsheet’s designs over time, after many hours of frustration. What can the average scientist learn from this? Take the time at the beginning of your project to design your spreadsheets well.
Too often we start collecting data and throw things in a spreadsheet to be sorted out later. The file morphs over time, sometimes for the better and sometimes for the worse. We end up copying and pasting columns and tables to create more visually appealing layouts. We insert links to other cells, other spreadsheets, and other files to minimize potential copy errors. We move files and need to update links. Etcetera. Rather than this rather winding path that often results in monotonous reorganization of data sets, consider thinking very carefully at the beginning of your project about the most effective way to design your spreadsheets.
It’s often easier said than done, but as Benjamin Franklin once said, an ounce of prevention is worth a pound of cure.
