Revenue by month and product
Rows hold Product, columns hold Month, values SUM Revenue from a Transactions tab with thousands of rows.
What is a pivot table in Google Sheets?
A pivot table summarizes a large table without writing SUMIFS for every combination. Drag fields into rows, columns, values, and filters to count deals by rep, sum revenue by month, or average scores by classroom. The pivot updates when you refresh the source range or change the pivot cache.
When to use it
Use pivot tables when you explore categorical breakdowns: sales by SKU and region, support tickets by tag, or survey counts by answer choice.
When to skip it
Skip pivots when you need row-level detail exports, complex custom columns, or a layout that must stay fixed cell by cell for printing. QUERY or manual summary tabs may fit better.
How it works
-
1
Select your data table with headers, then Insert > Pivot table and choose New sheet or Existing sheet.
-
2
Drag fields into Rows and Columns to define groupings, and into Values to pick SUM, COUNT, AVERAGE, or other aggregations.
-
3
Add fields to Filters to narrow the pivot without moving them into the visible grid.
-
4
Enable Show totals for row and column subtotals when leadership expects rollups.
-
5
Refresh pivot data after appending rows to the source or editing category labels.
-
6
Copy pivot layout to a new file only after reconnecting the source range in the destination workbook.
Examples in Google Sheets
Ticket volume by status
COUNT of Ticket id in Values with Status in Rows gives open queue size for a weekly ops email.
Average deal size by owner
AVERAGE on Amount with Owner in Rows highlights coaching opportunities without a custom QUERY string.
Build this without starting from a blank cell
Use a Better Sheets tool for Pivot table, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Source range stops at row 500 while data now reaches row 5000, silently dropping new records.
- Mixing text and numbers in amount columns so SUM shows zero or wrong totals.
- Double-counting rows duplicated in the source instead of deduplicating keys first.
- Renaming header cells without updating the pivot field list, which shows blank or old names.
- Building ten pivots on the same huge raw tab instead of one cleaned Input model tab.
Frequently asked questions
- Pivot table vs QUERY?
- Pivots are visual and fast to drag around. QUERY shines when you need one formula output or SQL-style logic in a cell.
- How do I refresh a pivot?
- Right-click the pivot or use the refresh option in the pivot editor when source data changed.
- Can pivot tables use formulas in source?
- Yes. Values are whatever the grid shows, including formula results. Fix types before aggregating.
- Pivot on multiple sheets?
- Combine data on one Input tab with QUERY or IMPORTRANGE first, then pivot that unified table.
- Percent of total in pivot?
- Use Show as > Percent of column or row in the value field settings when available.
- Pivot table grand total wrong?
- Check for blank rows, text numbers, duplicate keys, and filters hiding part of the source.
- Slicers on pivot?
- Add slicers from Data menu after the pivot exists to filter it interactively on dashboards.
- Copy pivot to another file?
- Paste the pivot and point it at data in the new file or use the same IMPORTRANGE source with care.
Related blog posts
Guides that explain Pivot table in more depth.
20 Google Sheets Tips for Bloggers
Through the use of computational tools, like AI and automation functions, you can gain insight from data that was previously too time consuming to extract.
Read post →20 Google Sheet Tips for Business Analysts
Learning Google Sheets empowers Business Analysts to efficiently analyze data, create insightful visualizations, collaborate with stakeholders, and streamline their data analysis workflows. It's a valuable tool for data-driven decision-making and problem-solving in a business context.
Read post →3 Google Sheet Tips for Administrative Professionals
Learning more skills in Google Sheets empowers administrative professionals to manage data efficiently, make data-driven decisions, collaborate effectively, and improve overall productivity.
Read post →Related glossary terms
filters in Google Sheets
Filtering hides rows that do not match criteria without deleting data. The filter toolbar on a header row is the fastest UI path. FILTER and QUERY formulas build dynamic views that update when source data changes, which suits dashboards and reporting tabs.
Read guide →dashboards
A dashboard is a single view that answers "how are we doing?" without hunting across tabs. In Google Sheets, it is usually a summary tab with big numbers, charts, and conditional formatting fed by QUERY, pivot tables, or IMPORTRANGE from raw data you keep elsewhere.
Read guide →grouping rows in Sheets
Row and column grouping adds outline controls on the left or top edge so readers expand or collapse sections. It is a presentation layer: formulas still see all cells whether collapsed or not. Grouping suits budgets, project plans, and any sheet with detail rows under subtotal headers.
Read guide →Slicer
A slicer is an on-sheet control that filters connected pivot tables and charts. Click category buttons instead of digging through filter menus. Slicers make dashboards feel interactive for executives and account managers who will not touch formula tabs.
Read guide →Done reading about Pivot table?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.