Group Data with Pivot Tables

By Jack Dougherty, last updated March 16, 2017

Here's a common problem: You open a large spreadsheet with many rows of data, such as a list of students. Your goal is to count students by categories, such as the number of students by each year of birth. What's the most efficient way to do this?

Screenshot: Long spreadsheet of student data
Screenshot: Long spreadsheet of student data

A solution: Create a pivot table to aggregate (or group together) and summarize data in another spreadsheet tab.

Screenshot: Pivot table of count by year of birth
Screenshot: Pivot table of count by year of birth

While pivot tables may look different across spreadsheet tools, the concept is the same.

Video with step-by-step tutorial for Google Sheets

1) Click this link and Save to download to your computer: sample-students in CSV format. CSV means comma-separated values, a generic spreadsheet format that most tools can easily open.

2) Sign into Google Drive (requires free account) and drag-and-drop the sample CSV file to instantly upload. Before you do this, make sure your Settings (gear symbol) is set to Convert Uploads to Google Docs editor format (the default setting).

3) Shift-click to select all columns that you wish to pivot.

4) Select Data > Pivot Table..., which opens a new spreadsheet tab.

5) In Report Editor, select Rows > Add Field > Year to list all entries in order.

6) In Report Editor, select Values > Add Field > Year to summarize all values for each entry.

7) Change Summarize by SUM to Summarize by COUNTA (to count alphabetical or numerical entries), or COUNT (to count only numeric values).

More Advanced Pivot Table with Google Sheets

In addition to grouping by rows, you can create more advanced pivot tables by grouping by columns and filtering results. For example, the pivot table shown below shows rows by birth year, columns by gender (blank, female, male, other), and filters results to show only 18 students from one country: US.

Screenshot: Advanced pivot table by year of birth and gender for US
Screenshot: Advanced pivot table by year of birth and gender for US

Learn More

Data Visualization For All is copyrighted by Jack Dougherty and contributors and distributed under a Creative Commons Attribution-NonCommercial 4.0 International License. You may freely share and modify this content for non-commercial purposes, with a source credit to http://DataVizForAll.org.
Improve this book: Donate to DataViz students and add comments or revisions.

results matching ""

    No results matching ""