How to Make a Pivot Table in Google Sheets

By Gerard Fernandez · Updated · 2 min read

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

  1. Click any cell inside your data. The table needs a header row, with one column per field (for example Category, Item, Amount).
  2. Go to Insert → Pivot table.
  3. 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

  1. Next to Rows, click Add and choose the field to group by, for example Category.
  2. 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 byAnswers the question…
SUMHow much in total per group?
COUNTAHow many rows per group?
AVERAGEWhat's the typical amount per group?
MAX / MINWhat'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:

  1. Next to Rows, click Add and choose Date - Month.
  2. Next to Values, click Add and choose the amount column (summarized by SUM).
Google Sheets Pivot table editor with Date - Month in Rows and Amount summarized by SUM in Values; the field list shows Date, Date - Month, Date - Day-Month and Amount
The field list offers Date - Month alongside the original Date column.
Pivot table result: Jan 200, Feb 245, Mar 150, Grand Total 595
Six dated expenses grouped into three months.

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.

  1. Click inside the pivot table to open the Pivot table editor (or use the Edit button under it).
  2. Next to Values, click Add and choose Calculated field at the top of the list.
  3. 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.
  4. Click Add.
Google Sheets Add calculated field window with name Revenue, formula =Units*Price, Summarize by SUM and the list of columns Region, Product, Units, Price
The Add calculated field window lists your column names on the right.

Why the totals can be wrong

Pivot table Revenue by region: East 14, North 90, South 120, Grand Total 588
With Summarize by: SUM, North shows 90. The real revenue is 40.

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.

Pivot table with Custom summarize and =Units*Price: East 14, North 20, South 16, Grand Total 20
Custom + =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)
  1. In the editor, click the pencil next to the calculated field's formula.
  2. Replace the formula with =SUMPRODUCT(Units, Price).
  3. Check that Summarize by says Custom, and click OK.
Google Sheets Edit calculated field window with formula =SUMPRODUCT(Units,Price) and Summarize by Custom
SUMPRODUCT multiplies each row's Units by its Price, then adds them up.
Pivot table Revenue by region: East 14, North 40, South 64, Grand Total 118
Correct revenue per region: 14, 40, 64, and a grand total of 118.

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"

Add calculated field window with formula =Units*Cost outlined in red and the Add button disabled
"Cost" isn't a column in the source data, so the formula turns red and Add is disabled.

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.