Summary (Pivot) Tables

This tutorial will take you through making summary tables. It will use an example of ~300 movie ratings collected in 1997-1998 from males and females between the ages of 14-18. See the Summary Tables Overview to review some key terminology and data statistics that can be used in summary tables.

Do the following:

➤ Create a copy of the "Teenage Movie Ratings" dataset on your Google Drive and open it in Google Sheets.

▶️ Watch this quick video that demonstrates the first few steps in this tutorial.

Making Summary Tables

We're first going to make a simple summary table that shows the average rating for every movie that's in the data. In most spreadsheet programs a Summary Table is called a pivot table.

Do the following:

➤ Using your own copy of the "Teenage Movie Ratings" data spreadsheet, choose Insert >> Pivot Table from the menu bar

➤ When prompted, select Insert to "New sheet", then click Create

This will bring up a new Pivot Table and the Pivot table editor.

Adding Rows

The Pivot table editor lets you choose what you want the rows, columns, and values to be in your summary table.

Follow the instructions below to make a table that displays the average rating for every movie listed in the data set. Set it up so that:

Each row is one movie.
Each value is the average rating of that movie.

Do the following:

➤ Using the Pivot table editor, select the Add button for Rows, then select movie from the list of options.

➤ Next, select the Add button for Values, then select rating from the list of options.

➤ From the rating options, select Summarize by AVERAGE

What Happened? Computation!

The power of the pivot table is that it allows you to compute things you could never do by just filtering and sorting. The pivot table is doing a lot of computing behind the scenes for you - which is great - but you should understand what's really happening so you can make your own choices in the future.

Here's a summary of what happened when you created the pivot table:

Rows - Group By: movie

- Rows act like the major category or grouping for what you want to calculate and display.
- When you set the rows to be "movie," the software finds all of the unique movie titles in the "Teenage Movie Ratings" dataset and puts one on each row.

Values - Display: rating; Summarize by: AVERAGE

- Values lets you specify the computation that should happen for each row.
- When you set value to be "rating" and summarized the rating by "average", the software calculated the average rating for each movie and displayed it next to the corresponding movie title.

Change the Value -- Summarize by: COUNT

Do the following:

➤ Change the rating's value to summarize by COUNT.

Now, instead of computing the average rating, this will count the number of ratings for each movie.

Add Another Field to Values

Let's show both the average rating and the count side-by-side in the table by adding another field to the Values.

Do the following:

➤ Add another Values field. The count is already there, so let's add the average rating again.

Now, we'll see the total number of ratings and the average rating for each movie.

Basics of Pivot Tables

There's not much more to it than that. Once you get the hang of pivot tables they can be a very powerful tool for working with data. There are more advanced things you can do with a pivot table, but you know enough now that you can experiment with the other settings and see what happens.

Key Ideas

Visualizing data. Yes, it's a table, but by grouping and summarizing information from a large data set, summary tables allow you to see things in the data you might otherwise not see.
Creating new data. Even for our simple movies example here, the raw data didn't contain the average rating for every movie, or count how many ratings there were. We had to compute it, and the pivot table let us do that quickly and easily.
Look at your data in new ways. Think: how could data be grouped? What could be calculated? Once you know how to make a summary table you can begin to look at raw data and ask questions that you know might be possible to answer.
The first step towards a good visualization. It can be difficult to make a meaningful chart or graph out of raw data. You often want to summarize it first, then chart it!

Now that you have learned the concepts of a summary table and created a basic table, you are now ready to proceed with more summary table features including, adding a column, filtering table data, and further manipulating the table data for visualization.

More Summary Table Features

Page updated

Google Sites

Report abuse