How do I build a pivot table to summarize my data?
A pivot table lets you drag column headers into Rows, Columns, and Values to instantly group and total your data, without writing a formula. Select your data, insert a pivot table, then drag a category column into Rows and a number column into Values to get a grouped total.
Formula (Excel and Google Sheets)
Insert > Pivot table (no formula required)A pivot table is best understood as an automated version of "group by category and total the numbers," the same operation SUMIF and COUNTIFS perform, but built through drag-and-drop instead of formula syntax. You choose one or more columns to group by (Rows), optionally a column to split into side-by-side columns (Columns), and a number column to total, count, or average (Values).
The result updates automatically if your source data changes and you refresh it, which makes a pivot table a better fit than a formula-built summary for data that gets updated regularly, like a monthly export.
Step by step
- 1
Select your data range, including headers
Make sure every column has a header; a pivot table uses those as field names.
- 2
Insert a pivot table
In Excel: Insert > PivotTable. In Google Sheets: Insert > Pivot table. Choose to place it in a new sheet.
- 3
Drag a category column into Rows
This is what your summary will be grouped by, e.g. region or product.
- 4
Drag a number column into Values
This is what gets totaled. Confirm it is set to Sum (or Count, or Average) rather than another summary type.
- 5
Refresh after the source data changes
A pivot table does not update automatically; use the refresh option after editing the underlying data.
Common questions
Do I need to know any formulas to use a pivot table?
No. The grouping and totaling happen through drag-and-drop fields, not formula syntax.
Why doesn't my pivot table show new rows I added to the source data?
Pivot tables do not auto-refresh. Right-click the pivot table and choose Refresh, or expand its source range to include the new rows.
Related formulas
How to Use SUMIF and SUMIFS in Excel and Google Sheets
RelatedHow to Use COUNTIF and COUNTIFS in Excel and Google Sheets
RelatedHow to Calculate Percentage Change Between Two Numbers
RelatedHow to Use SUMPRODUCT for Weighted Totals and Multi-Condition Counts
From the blogHow to Make Sense of Your Spreadsheet Data Without Being an Analyst
Want a formula written for your exact spreadsheet, not a generic example?
Try the free formula generator