Pivot tables
9 minutes · 4 steps · free, no sign-up
A pivot table answers questions like "how much did each region sell?" in a few clicks. It takes a long list of rows, groups them by one column and summarises another. You could do the same with SUMIF formulas, but a pivot table builds the whole summary for you.
- Summarise a list by a column using a pivot table
- Choose between sum, count and average
- Group by a different column
- See the summary follow changes in the data
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceThe ideas
Rows the column to group by (Region)
Values the column to summarise (Sales)
Summarise by SUM, COUNT or AVERAGE
Grand total the overall figure at the bottomThe key ideasSummary: Pivot tables
A pivot table has rows (the groups, one row per distinct value) and values (what to calculate for each group). Choosing SUM gives totals, COUNT gives how many rows, AVERAGE gives the typical amount. Pick the data and the headings, and the table is built.
In this practice sheet the table is made of live formulas, so it follows the data: change a sale and the totals change with it. In real Google Sheets a pivot table is a special object that refreshes automatically, and can also group by columns across the top.
Watch out forCommon mistakes
- Leaving out the headings. Include the header row in the range. The pivot table uses it to name the groups and columns.
- Messy group names. "North" and "north " count as two groups if spaces differ. Clean the data first (see the cleaning lesson).
- Summarising by SUM when you meant COUNT. To count how many sales happened, use COUNT. To total the money, use SUM.
- Typing over a pivot table. Its cells are generated. Change the data, not the summary.
Next stepTry it in the real Google Sheets
In Google Sheets choose Insert → Pivot table, then add Rows, Columns, Values and Filters in the side panel. Pivot tables can also be grouped by date, and you can use several fields at once.