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?
A solution: Create a pivot table to aggregate (or group together) and summarize data in another spreadsheet tab.
While pivot tables may look different across spreadsheet tools, the concept is the same.
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).
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.
- Google, Create and Use Pivot Tables Help Page https://support.google.com/docs/answer/1272898
- LibreOffice, Creating Pivot Tables Help Page https://help.libreoffice.org/Calc/Creating_Pivot_Tables
- Andrew Ba Tran, "Tutorial: How to Make Pivot Tables in Google Sheets," TrendCT, September 4, 2015, http://trendct.org/2015/09/04/tutorial-how-to-make-pivot-tables-in-google-sheets
Improve this book: Donate to DataViz students and add comments or revisions.