Lab 13 - Spreadsheets: an introduction

We deal with tabular data or table data all the time without probably even recognizing it. If you can easily think of a group of similar items, like the classes you are taking this term, those can be rows in a table. The columns of the table are characteristics that apply to each of the row items. We could think of the columns in our current classes example as having labels like location, instructor, time, and so on. From your schedule of classes to your tax records to your workout plan, a great deal of information is organized into rows and columns. Spreadsheet applications, like Microsoft Excel, OpenOffice Calc, and Google Spreadsheets not only allow us to record that data, but also to analyze it and visualize it.

To consider:

Tables and charts

In this thirteenth lab, we'll do some hands-on work with spreadsheet applications. As you explore, review your answers from the To consider: section above. Have you changed your mind about any of them?

Learning more

  1. Spreadsheet applications are powerful tools that can do more than organize information into tables. They can be capable of performing complex mathematical functions, perform statistical analysis, and more. In addition to the in-application help menus, knowing where to find help online is one of the first tools to put in a good toolbox.

  2. Now that you know where to find help if you get stuck, let's get started with spreadsheets. Instead of having to create tables and their data from scratch, you'll download someone else's tabular data and import it into a spreadsheet application. Then you'll make some modifications to that data and its arrangement. You'll want to start by looking for comma-delimited, tab-delimited, or otherwise regularly formatted data files. Delimiters are characters that separate table cell values from each other, and your spreadsheet program will need to know which delimiter is being used to properly import the file. Once the file is imported properly, then it can be saved in your application's native format.

    You may find that your university, college, or school collects demographics, or statistical population characteristics, about the institution, so you can always look for tabular data there. That data may already be in a spreadsheet format for download. You can usually find this infomation my searching your institution's site for institutional research, in addition to searching for demographics or statistics.

    Try IT: Getting regional data from Minnesota
    You can download data from Minneapolis/St. Paul's Metropolitan Council. Here's their download page for tabular data. Follow the on-screen instructions, then when the result is returned in the browser, click the Export to .CSV button to save the file to your computer. In this case, a .zip file is downloaded which contains other files. Make a note of where the .csv is, the follow the directions for your spreadsheet application to import it or open it.

    • Before you start making changes, take a look at the column labels and the values in the rows. Can you tell what you are looking at? Are the labels and values in plain language?
    • Were there any formatting errors or cell values that appear to be shifted into the wrong columns? If so, how could you fix those errors?
    • Now use your spreadsheet application to sort by different columns. Does it make the information more clear, less clear, or about the same? Why?
    • Can you tell if the spreadsheet is using any formulas? How would you be able to tell?
    • Choose two cells with numeric values and write a formula that adds those two values together. Change the display of that cell value so the resulting number is red.
    • Select a region of cells (or the entire table) and see if you can make a chart or graph using your spreadsheet application. Is it easier to see the data values this way, or harder? Why? Did seeing the data a different way change how you thought about the data?
    • If you haven't already done so, walk through the exercises and examples from the book. Could any of those techniques apply to the data you downloaded?

  3. Working with spreadsheets isn't always so serious. Did you know that Microsoft Excel developers have put Easter eggs in some versions of that application? If you still have Excel 97, you can enjoy a flight simulator right in the application. If you've upgraded long ago, you can watch the video:

    There are other Easter eggs, but to celebrate the release of the Office 2010 suite, Excel developers didn't try to hide anything. They created and promoted two downloadable games to play in Excel 2010: Missile Command and Tower Defense.

Moving on

We have just scratched the surface of what's possible with spreadsheets. Have you seen IBM's shared data visualization project ManyEyes? It's a platform for collecting datasets so that you and others can use ManyEyes' toolsets to create visualizations and share them, as well as comment on and rate them. One of the more highly rated visualizations is user Mark Wattenberg's chart showing the Titanic's survival rates broken out by gender and class of passage (First Class, Second Class, Third Class, Crew). Do you think this data would be as interesting to you or as easy to understand if you saw it in a tabular format? Good data or information visualizations can very quickly answer questions like, "Who had a better survival rate: male or female Titanic passengers?".

Are there any ManyEyes datasets that have interesting information for you? Try picking one and creating a sample visualization. Was it hard to do? Did it tell you anything about the data that surprised you when you saw it?