Pivot tables are one of the most useful things Google Sheets can do. If you have a big flat table of data and want to slice it by category, sum it by month, or count transactions by rep, a pivot table gets you there in about two minutes, no formulas required.
This guide walks you through the whole process, start to finish. By the end, you'll know how to build a pivot table in Google Sheets, how to customize it, and what to do when things don't work the way you expect.
Before You Build: Get Your Data in Shape
Pivot tables work from a flat table of data. That means one row per record, one column per field, and a header in the first row. If your data has merged cells, subtotals baked in, or blank rows breaking things up, clean those up first. Google Sheets will struggle to read the range correctly otherwise.
A clean dataset looks something like this:
Date | Rep | Region | Product | Amount
2026-01-03 | Sarah K | West | Pro Plan | 1200
2026-01-07 | Marcus T | East | Starter | 480
2026-01-11 | Sarah K | West | Pro Plan | 1200
Every row is one transaction. Every column has a header. No gaps, no totals row, no merged cells. That's all you need.
How to Create a Pivot Table in Google Sheets
Click anywhere inside your data. Then go to Data in the menu bar and click Pivot table. Google Sheets will ask whether you want to insert it in a new sheet or the existing one. New sheet is almost always the right call — it keeps your source data clean and gives the pivot table room to expand.
Once you click Create, a blank pivot table opens in a new tab, and the Pivot table editor panel appears on the right side of the screen. This is where you do all the work.
Step 1: Add Your Row Field
Click Add next to Rows and choose the field you want to group by. If you're summarizing sales by rep, choose the Rep column. If you want to see revenue by region, choose Region. This becomes the left column of your pivot table, with one row per unique value in that field.
Step 2: Add Your Value Field
Click Add next to Values and choose the field you want to calculate. For a revenue summary, that's your Amount column. Google Sheets defaults to SUM, which is usually what you want. If you need a count of transactions instead of a total, click the SUM dropdown and change it to COUNTA.
At this point you already have a working pivot table: one row per rep (or region, or whatever you chose), with the total for each.
Step 3: Add Columns to Break It Down Further
If you want to see how each rep performed by month, or how each region broke down by product, click Add next to Columns and choose the relevant field. Date fields work especially well here: after adding Date as a column, click on it in the editor and change the grouping to Month or Quarter. Google Sheets will collapse all the individual dates into monthly buckets automatically.
Step 4: Add Filters If You Need Them
If you only want to look at one region, one product, or one time period, add a filter instead of restricting your source data. Click Add next to Filters, choose the field, and then use the dropdown that appears in the pivot table itself to select what to include. Filters are non-destructive — you can remove them or change them at any time without touching the original data.
Changing How Values Are Calculated
By default, number fields sum and text fields count. But you can change that. Click on any field in the Values section of the editor and a dropdown appears with options: SUM, COUNT, COUNTA, AVERAGE, MAX, MIN, and a few more specialized ones.
COUNTA counts every non-empty cell, which is useful when you want to count rows that contain text (like rep names or product names) rather than numbers. COUNT only counts numeric values.
If you want to show percentages instead of raw totals, look for the Show as dropdown inside the Value field settings. You can display values as a percentage of the row total, column total, or grand total without writing any formulas.
Sorting and Formatting
Pivot tables in Google Sheets sort alphabetically by default. To change that, click the dropdown next to the row or column field in the editor and switch the sort order. You can sort by the label (A to Z or Z to A) or by the summarized value (highest to lowest or lowest to highest). Sorting by value is useful when you want the biggest regions or the top reps at the top.
For formatting, just treat the pivot table cells like any other cells in Sheets. Select the value column, hit Format, and apply a currency or percentage format. You can also use conditional formatting on the pivot table output to highlight values above or below a threshold, though the formatting won't survive a full refresh if the data structure changes significantly.
Refreshing When Your Data Changes
Google Sheets pivot tables update automatically when the source data changes, as long as the pivot table is pointed at the right range. If you add new rows at the bottom of your dataset, they'll be picked up on the next recalculation.
If you've added columns or shifted your data around, you may need to update the data range manually. Click anywhere in the pivot table, look at the editor, and you'll see the source range at the top. Click the range field to update it.
The Easy Way: Using SheetXAI Inside Google Sheets
If you'd rather skip the manual steps, SheetXAI works directly inside Google Sheets as a sidebar. You describe what you want in plain language and it builds the pivot table for you.
Example 1: Your data is already in the sheet.
Say you have a table of sales data on Sheet 1 and you want a summary by rep and month. You open the SheetXAI sidebar and type something like:
"Create a pivot table from the sales data showing total revenue by rep, broken down by month."
SheetXAI reads your sheet, figures out which columns map to what, builds the pivot table on a new tab, and formats the values. You don't touch the Pivot table editor at all.
Example 2: Your data lives somewhere else.
Now say the data isn't in your sheet yet. It's sitting in your CRM, your accounting software, or wherever your business data actually lives. Normally that means an export, a paste, some cleanup, and then the pivot table. SheetXAI skips all of that. You describe the whole thing at once:
"Pull my closed-won deals from the last 90 days and build a pivot table showing total revenue by rep and region."
It pulls the data, drops it into the sheet, and builds the pivot table on top of it. Same result, none of the manual steps in between.
For a one-off analysis on data you already have, the manual approach above works great. For anything recurring, or anything that starts with an export from another system, try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Dashboard in Google Sheets, How to Automate Reports in Google Sheets, and Google Sheets AI Guide.
