Skip to Main Content

Escape from Spreadsheet Hell

The website for the workshop! TL;DR we’ll teach you how to create spreadsheets optimized for reuse by you and others. Your future self will thank you.

What is a spreadsheet?

The goal of this guide is to teach you how to make spreadsheets that make people and software happy. To do this you'll need to learn computers like and don't like and how to structure data in a way they understand. Learning these things isn't hard and will turn spreadsheets from a source of frustration into a powerful tool.

Spreadsheets are a file format consisting of a digital grid. Spreadsheet files were made to be electronic surrogates for the paper ledger books once used by accountants and spreadsheet software (such as Excel or Google Sheets) was built to address a specific type of data: data stored in ledgers that needed to be processed to answer questions.

 

Merchant ledgers recorded line item purchases with entries for date, item bought, unit price, and total price, with one item making up each row of an entry.

A image depicting a page of the ledger of William Ramsay, a shop keeper in Alexandria, Virginia in the mid-1700's. Ledgers such as these are the precursors of spreadsheets: they organized purchase and expense data into rows of purchases and columns of prices and expenditures. Visit the Smithsonian's interactive exhibit (opens in new window) to learn more.

 

However, the flexible nature of its grid format has lead to spreadsheets being used for much more than data processing. The modern spreadsheet has become a catch all tool for data storage, data entry, data visualization, print layouts, and even art.

And yet, the underlying assumption of spreadsheet software is that if you put something in a spreadsheet, you must want to process it. This is why spreadsheet programs surprise us with strange or undesired behavior such as:

  • Change dates to, what appear to be, random numbers,
  • Give numbers two decimal places by default,
  • Change numbers beyond a certain number of digits to scientific notation,
  • Offer to autofill values, but only vertically.
  • Other goofy and unexpected things.

In summary: spreadsheet software was made for a specific purpose and assumes your data is of a certain type and organized in a specific way. If your data does not fit the purpose or organization the software expects then you are more likely to run into problems and frustrations with your spreadsheets. The next part of this guide will introduce you to Tidy Data - a data standard for spreadsheets that is people and software friendly.

A video showing a very creative use spreadsheets used to create a video of a man dancing set to the song "Cuban Pete".

Escape Coordinators

Megan O'Donnell
mno@iastate.edu
Data Services Librarian
ENT, EEOB, NREM, and Environment.

Kris Stacy-Bates
kksb@iastate.edu
Science and Technology Librarian
ABE, CCEE, Math, and Stats