How to Make a Pivot Table in Google Sheets
A pivot table turns a long list into a short summary without a single formula. Total sales per region, expenses per category, orders per month: you drag a few fields into place and Google Sheets does the grouping and adding for you.
Create a pivot table
- Click any cell inside your data. The table needs a header row, with one column per field (for example Category, Item, Amount).
- Go to Insert → Pivot table.
- Check the Data range, choose New sheet, and click Create.
Sheets opens a new tab with an empty pivot table on the left and the Pivot table editor on the right.
Add rows and values
- Next to Rows, click Add and choose the field to group by, for example Category.
- Next to Values, click Add and choose the field to total, for example Amount.
The editor also shows Suggested pivot tables at the top, such as "Sum of Amount for each Category". Clicking one builds the same table in one step.
That's it. Each category appears once, with its total next to it, and a Grand Total at the bottom.
Change SUM to COUNT, AVERAGE and more
In the Values section, open the Summarize by list under your field (Summarise by if your Google account uses UK English) and pick another calculation:
| Summarize by | Answers the question… |
|---|---|
| SUM | How much in total per group? |
| COUNTA | How many rows per group? |
| AVERAGE | What's the typical amount per group? |
| MAX / MIN | What's the biggest or smallest per group? |
You can add the same field to Values twice, for example once as SUM and once as COUNTA, to see the total and the number of purchases side by side.
Add columns and filters
- Columns: add a second field here to make a grid, for example categories down the side and months across the top.
- Filters: add a field to include only some values, for example only this year's rows.
Keep the pivot table up to date
Pivot tables update automatically when the source data changes. If you add rows below the original range, open the editor and extend the data range, or use an open-ended range like Expenses!A1:C from the start.
Tip: you can't type inside a pivot table. To change the numbers, edit the source data. To reuse the results elsewhere, copy them and use Edit → Paste special → Values only.
Group dates by month
When your data has a date column, the Pivot table editor lists extra fields next to it, such as Date - Month and Date - Day-Month. You don't need a helper column:
- Next to Rows, click Add and choose Date - Month.
- Next to Values, click Add and choose the amount column (summarized by SUM).
Closed the editor and can't get it back? Click inside the pivot table and use the small Edit button under it; see pivot table editor not showing.
Need Revenue = Units × Price? Add a calculated field, but check the totals: see pivot table calculated fields.
Calculated fields (and why their totals can be wrong)
A calculated field adds a column to a pivot table that doesn't exist in your data, like Revenue = Units × Price. It's easy to add, but in our test the obvious setup gave wrong totals without any warning. Here's how to add one, why the numbers can be off, and the formula that gave the right result.
Add a calculated field
Our data has Region, Product, Units and Price for five sales, and a pivot table with Region in Rows.
- Click inside the pivot table to open the Pivot table editor (or use the Edit button under it).
- Next to Values, click Add and choose Calculated field at the top of the list.
- In the Add calculated field window, give it a name (Revenue) and type the formula using the column names, for example
=Units*Price. A green check means the formula is valid. - Click Add.
Why the totals can be wrong
North had two sales: 10 units at 2 and 5 units at 4, so the revenue is 10×2 + 5×4 = 40. The pivot table showed 90. That's 15 (total units) × 6 (total of the prices): with Summarize by: SUM, the formula multiplied the sums of the columns instead of multiplying row by row and adding up. The grand total, 588, was just as wrong (the real total is 118).
East looked right (14) only because it had a single sale. That's what makes this mistake easy to miss: small groups come out correct.
Switching to Summarize by: Custom with the same =Units*Price didn't help either: North showed 20, the first sale only.
=Units*Price: each group only uses its first row.The fix: Custom + SUMPRODUCT
To multiply row by row and then add, use SUMPRODUCT with Summarize by: Custom:
=SUMPRODUCT(Units, Price)
- In the editor, click the pencil next to the calculated field's formula.
- Replace the formula with
=SUMPRODUCT(Units, Price). - Check that Summarize by says Custom, and click OK.
The same rule applies to any calculated field that multiplies or divides two columns: build the per-row calculation inside SUMPRODUCT (or another function that works on whole columns), and use Custom.
"Unknown column name"
A column name that doesn't exist in the source data turns the formula box red, shows "Unknown column name: 'Cost'" and greys out the Add button. Click a name in the Columns list on the right to insert it exactly as Sheets expects. If a name has spaces, wrap it in single quotes: ='Unit price'.
Frequently asked questions
Where did the Pivot table editor go?
It only shows while a cell in the pivot table is selected. Click any cell of the table and it reappears (look for the Edit button if it stays hidden).
Why does my pivot table show a blank row?
The data range includes empty rows. Tighten the range, or add a filter on the Rows field to exclude (Blanks).
Is there a formula alternative?
Yes. SUMIF gives totals per category, and the QUERY function can group and sum in one formula.