PivotTables: summarising data in seconds
Summarise any list with a PivotTable: prepare data, drag fields, group dates, change calculations, refresh and troubleshoot.
In this lesson you will
- Prepare data for a PivotTable
- Build a table by dragging fields
- Change calculations and add slicers
- Refresh and troubleshoot
About 16 minutes
Ten thousand rows, one clear table
Gloria exports a year of orders from her shop, 9,000 rows. Her partner asks, ‘Show me sales by region and month.’ With SUMIFS she would need a formula for every region and month, set up by hand. A PivotTable answers the question by dragging four boxes. It is the feature that most surprises people who have used Excel for years without it, and it is the standard tool for summarising data.
What a PivotTable is
A PivotTable is a live summary of a list. You choose which field goes down the side (Rows), which goes across the top (Columns), what to add up (Values) and what to narrow down (Filters). Excel does the grouping and the sums. It never changes your original data, and you can rearrange it in seconds.
Prepare the data
- One header row, with a unique name in every column.
- One record per row, no blank rows or columns, no merged cells.
- No subtotals inside the data.
- Real dates and real numbers, not text (lesson 2).
- Make the list an Excel Table with Ctrl+T so the PivotTable can grow with it.
Build one
- Click any cell in the data.
- Choose Insert, PivotTable, and accept New Worksheet.
- In the field list, drag Region to Rows.
- Drag Date to Columns. Excel groups dates into months automatically in modern versions; right-click a date and choose Group if it does not.
- Drag Revenue to Values. It becomes Sum of Revenue.
- Click inside the table and use PivotTable Analyze to refresh, format and filter.
What the table would show
The sandbox cannot create a real PivotTable, so the result below was produced another way: SUMIFS formulas that give the same answer a PivotTable would, calculated for real and checked against an independent Python grouping. It is the sum of revenue by region and month for the twelve sample sales:
| Region | Jan | Feb | Mar |
|---|---|---|---|
| North | 680 | 0 | 660 |
| South | 300 | 300 | 240 |
| East | 0 | 580 | 540 |
| West | 0 | 360 | 500 |
| Total | 980 | 1240 | 1940 |
The grand total is 4160, and splitting it by product gives Pen 1640 and Notebook 2520, which add back to the total. In a real PivotTable you would see these same figures, with a Grand Total row and column added for you. If your PivotTable disagrees with a total you can check another way, find out why before you trust either.
Changing what you see
| Action | Result |
|---|---|
| Drag Product into Columns | A region by product table |
| Click Sum of Revenue, Value Field Settings, Average | Average sale instead of total |
| Value Field Settings, Show Values As, % of Grand Total | Shares of the whole |
| Add Region to Filters | A drop-down to show one region |
| Insert, Slicer | Clickable buttons that filter |
Refresh and layout
- A PivotTable does not update when the data changes until you right-click it and choose Refresh (or set it to refresh on open).
- Choose Design, Report Layout, Show in Tabular Form for a flat, readable look.
- Double-click any number to see the rows behind it on a new sheet.
- Add a PivotChart from PivotTable Analyze to chart it. It always matches the table.
Troubleshooting
| Problem | Likely cause |
|---|---|
| Values show Count instead of Sum | Some cells in the column are text or blank |
| Dates are not grouped | They are stored as text, or the column has blanks |
| New rows are missing | Data was not a Table; refresh and change the source |
| Names appear twice | Spelling or spacing differences in the data |
Practice
Take a list of at least twenty sales rows with date, region, product and amount. Build a PivotTable with Region down the side and Month across the top. Add a slicer for Product. Then compare one cell with a SUMIFS formula to check the PivotTable is right.
The PivotTable cell and the matching SUMIFS should agree to the last digit. If they differ, the likely cause is hidden spaces or different spellings, which a PivotTable treats as separate items, or text amounts that were counted instead of added.
Forgetting to refresh. You change the data, look at the PivotTable and believe the old numbers. Make Refresh a habit, or tick Refresh Data when Opening the File.
Grouping and calculating inside a PivotTable
Right-click any date in the Rows or Columns area and choose Group to roll dates into months, quarters or years. Right-click numbers and choose Group to make bands such as 0-99, 100-199. For percentages, use Show Values As: ‘% of Column Total’ shows each month’s share within a region, and ‘% of Grand Total’ shows each cell’s share of everything. These are the figures managers ask for most, and they need no formulas. If you do need your own calculation, Calculated Field under PivotTable Analyze lets you define one, such as revenue divided by units.
When a PivotTable is the wrong tool
A PivotTable shines on long lists with repeated categories. It is awkward for a small fixed report with a special layout, where SUMIFS gives you full control, and for data that is not a clean list. Pick the tool that matches the job, and when in doubt, build both and compare one number.
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
Which area of a PivotTable holds the numbers that are added up?
- Filters
- Rows
- Columns
- Values
Revenue goes in Values.
Does a PivotTable update automatically when the data changes?
- Yes always
- No, you must refresh it
- Only on Fridays
- Only when printed
Right-click and choose Refresh.
Why prepare data with one header row and no blanks?
- Excel forbids blanks
- It sorts faster
- It adds colour
- PivotTables need a clean list
Messy data gives messy tables.
What does double-clicking a number in a PivotTable do?
- Shows the rows behind it
- Deletes it
- Sorts it
- Merges cells
It is a quick way to check a total.
Values show Count instead of Sum. What is a likely cause?
- The table is too small
- Dates are grouped
- The column contains text or blanks
- The file is saved
Clean the column and refresh.
Summary
- PivotTables summarise lists by dragging fields into Rows, Columns, Values and Filters.
- Clean data first: headers, no blanks, real dates and numbers.
- Refresh after the data changes.
- Check a PivotTable cell against a formula.
Questions
Is a PivotTable better than SUMIFS?
For quick, flexible summaries, yes. SUMIFS is better when you need a fixed layout that feeds other formulas.
Can I use a PivotTable on data from another file?
Yes, using Data, Get Data to connect, but start with data in the same workbook while you learn.
Do PivotTables exist in Google Sheets?
Yes. Use Insert, Pivot table. The idea is the same, with a slightly different editor.
