What Ben Franklin Thinks about Spreadsheets

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.

Ben Franklin

Benjamin Franklin wants you to to think about your spreadsheet design before you start collecting your data. From Flickr by elycefeliz

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.

Tagged , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: