Skip to content

Potentially Problematic Excel Features

Posted in UC3

This post wraps up my report from the ESA conference, but I saved some of the best info for last.  I asked ecologists what features they used in Excel in the course of their research. There’s no question that this group of scientists is using Excel to its full potential.  However some of the features that make Excel so great cause major problems when trying to archive data.  Here’s what people are using:

Features used in Excel
Percent of Ecologists surveyed who use a particular feature of Excel

Not surprisingly, everyone is using the formulas available in Excel.  This is certainly one of Excel’s greatest strengths, and what makes it universally useful across disciplines and applications (I know I sometimes open Excel rather than a calculator to do simple arithmetic).  While at ESA I had an interesting conversation with an ecologist who possessed a 30-year data set of tree diversity.  Some of the data in his Excel spreadsheets were contained within embedded formulas.  These data would be lost if the spreadsheet were exported as a .csv file, but he does not have the personnel or funds to manually extract the data.  This is an interesting challenge we are considering in light of the Excel add-in.

The phrase “cell shading” indicates that color-coding in cells is to convey information about the data.  Essentially, the shading is metadata that is only visible in Excel.   This is bad news for data archiving since any color-coding will be lost if data are exported as any type of file except .xls.  The same problems go for using the “Comments” feature in Excel- these potentially important nuggets of information are lost outside of the software program.

Other potentially tricky features for a data archive to handle are multiple tabs in a file, multiple tables in a tab, the ever-popular pivot tables, and macros.   A somewhat comforting statistic is that ecologists are consistently labeling their columns.  This means there exists at least some metadata in a given Excel spreadsheet.

Next week’s post will pertain to the American Fisheries Society Meeting in Seattle, WA that I attended this week.  Here’s a sneak peek: fisheries folks are on average much less likely to share data (!).

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *