I'm drowning in data...PivotTables to the rescue!

Data Moves & Topics December 9, 2019

By: Kristin Hunter-Thomson

When working with large datasets, such as long or frequently sampled time series, it can be overwhelming to think about using all of the data at once. Let's explore an example.

We are going to investigate the monthly average sea ice extent around Palmer Research Station in Antarctica using data from the Palmer Long Term Ecological Research project. By accessing the data through the online data portal, Datazoo, we can download a .csv file of the data which looks like this...

Note, .csv file indicates that it is a file with data in which the data are comma-separated values. This enables a .csv file to be opened by almost any program, Excel, Google Sheets, etc.

The good news is that this dataset has a great amount of data for us to use!

The downside is that there is SO much data!

Note, you will need to do some initial data processing of the data file. For example, you need to understand how the data were collected. Some scientists use 999 or -999 or NAN or NaN to indicate where no data were collected. You will need to decide what to do with these missing data.

You can manually calculate the average value of sea ice extent by month for each month. Or you could have Excel do it for you via equations by typing "=average(number1:numberlast)"...

Sometimes introducing students to writing equations in Excel can be cumbersome and more than you want to take on with your students. There is another way in which you can make Excel do the calculations for you...a PivotTable.

HERE IS HOW TO MAKE PIVOTTABLES YOUR BEST FRIENDS!

1. Highlight all of the data.

2. From primary navigation menu, select "Data" --> "Summarize with PivotTable"

3. Confirm the range of data are correct in the "Select a table or range" and decide where you want the PivotTable to be placed in the "Choose where to place the PivotTable"

4. Set up the PivotTable the way you would like the calculations to be made. As we are looking for the monthly average across the full time series...

1. Click and drag each month into the Values section.

2. Change the values from "Sum" to "Average" by selecting on the "i" icon in a circle.

3. Repeat for each month.

While it still requires a couple steps to complete the full PivotTable, it takes far less time than calculating the average per month by hand. If the intention of the work with the data is for students to interpret and analyze the data, then help them get there faster!

Note, if you are interested in more guided assistance to help your students download data from Datazoo, check out these step-by-step instructions from June 2017.

Also, note that all of the provided instructions and screenshots are based off of Excel version 15.32.