Pivot Tables in Excel and Google Sheets: A Beginner's Guide With Examples
If you've ever filtered a list over and over to add up 'how much per person' or 'how much per month', a pivot table does that in a few clicks.
Start with clean data
- One header row, with a unique name for every column
- One record per row: a date, a category, an amount
- No blank rows or columns, no merged cells and no subtotal rows inside the data
- Format as a table in Excel (Ctrl+T) so new rows are included automatically
Example data: a list of holiday purchases with columns Date, Person, Category, Shop and Amount, like the one in our Christmas budget tracker.
Make a pivot table in Excel
- Click any cell in the data.
- Go to Insert > PivotTable (choose From Table/Range if asked).
- Check the range and choose New Worksheet, then click OK.
- In the PivotTable Fields pane, drag Person to Rows and Amount to Values.
- You now have total spending per person. Drag Category to Columns for a person-by-category grid.
Make a pivot table in Google Sheets
- Select the data, including the headers.
- Go to Insert > Pivot table, choose New sheet and click Create.
- In the editor on the right, click Add next to Rows and pick Person.
- Click Add next to Values and pick Amount (summarise by SUM).
- Add Category to Columns if you want the grid.
The four areas, in plain English
| Area | What it does | Example |
|---|---|---|
| Rows | One line per unique item | Each person |
| Columns | One column per unique item | Each category |
| Values | The number to calculate | Sum of Amount |
| Filters | Limit what is counted | Only December |
Useful tricks
- Count instead of sum: Excel: click the field in Values > Value Field Settings > Count. Sheets: change 'Summarise by' to COUNTA
- Percent of total: Excel: Show Values As > % of Grand Total. Sheets: 'Show as' > % of grand total
- Group dates by month: Excel: right-click a date in the pivot > Group > Months (newer versions often group dates automatically). Sheets: right-click a date > Create pivot date group > Month
- Sort biggest first: right-click a value > Sort > Largest to Smallest (Excel), or set 'Sort by' in the Sheets editor
- Top 10 only: Excel: row label filter > Value Filters > Top 10. Sheets: add a filter by condition
Refreshing
In Excel, a pivot table doesn't update by itself. After you change the source data, click inside it and choose PivotTable Analyze > Refresh (or Data > Refresh All). In Google Sheets, pivot tables update automatically when the source data changes.
Common mistakes
- Amount shows as 'Count of Amount': some cells in the column contain text or blanks. Fix the data, then change the field to Sum
- New rows are missing: the source range ends before them. Use a table (Ctrl+T) in Excel or select whole columns (A:E) in Sheets
- The same name appears twice: spelling or extra spaces differ. Clean the data with TRIM, or remove duplicates
- '(blank)' row: the range includes empty rows
Pivot table or formula?
For a fixed report that must look the same every time, SUMIFS and COUNTIFS or the FILTER function can be easier to format. For exploring data and answering questions quickly, a pivot table wins.
Quick FAQ
What is a pivot table used for? Summarising a big list, such as totals, counts or averages by category, person or month, without writing formulas.
Does Google Sheets have pivot tables? Yes: Insert > Pivot table. They work like Excel's and update automatically.
Why isn't my Excel pivot table updating? Excel pivot tables need a manual Refresh (PivotTable Analyze > Refresh) after the source data changes.
Sources: Microsoft Support: Create a PivotTable to analyze worksheet data; Google Docs Editors Help: Create and use pivot tables.