Library Guides: Escape from Spreadsheet Hell: What is a spreadsheet?

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:

Changing dates to, what appear to be, random numbers,
Giving numbers two decimal places by default,
Changing numbers beyond a certain number of digits to scientific notation,
Offering 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.