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:

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 (!).

### Like this:

Like Loading...

*Related*

There’s a command in Excel (I forget what it is) to replace formula cells with formula values. (One gotcha; it only works one sheet at a time.) That helps with the .csv problem, but there’s still information loss — the formula itself would have to be reverse-engineered from the result. If it’s non-obvious…

Thanks Dorothea! I think the command is [Control + ~] (tilde): that shows you all of the formulas on a particular sheet. You can then save as a .csv file. You are right, though- you would still need to pull out those numbers from the formula text. The site I found this from is http://www.mrexcel.com/articles/show-formulas-in-cells.php

I found about this project through Lee Dirks and am enjoying.This is an excellent topic and i like to share an issue I often have with library metadata. We are bringing data into Excel to manipulate that is in utf-8 or another format than Unicode-16. When Excel encounters a character it can’t decipher, it substitutes various symbol combinations. many of these are known problems and we programatically clean-up the data with find and replace. Nevertheless if you are moving data sets into Excel from other applications, character recognition can be a problem..

[…] dataset changes is known as provenance; generally Excel is terrible at documenting provenance. I wrote about this a few years ago on the blog, and we mentioned a few of the more egregious ways people abuse Excel in our […]

[…] and prevents researchers using Excel from producing fully replicable research. Read “Potentially Problematic Excel Features” to learn more about the pitfalls of […]