PivotTables essentials
Summarize a tidy source and verify what the aggregation actually means.
By the end, you’ll be able to:
- Prepare a source with one record per row
- Place fields in Rows, Columns, Values and Filters
- Verify aggregation, scope and refresh behavior
Begin with a tidy source
A PivotTable groups source records and calculates a summary. It works best with one header row, a field in each column and a consistent record type in each row. Remove manual subtotal rows from the source so they are not counted alongside their details.
For this log, each row is one order. Revenue must be numeric, Region and Channel must be consistent labels, and every column needs a useful header. Turn A1:E7 into an Excel table to help the source range expand when records are added.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Region | Channel | Units | Revenue |
| 2 | S101 | East | Online | 4 | 120 |
| 3 | S102 | West | Store | 2 | 80 |
| 4 | S103 | East | Store | 5 | 200 |
| 5 | S104 | West | Online | 3 | 150 |
| 6 | S105 | East | Online | 2 | 60 |
| 7 | S106 | North | Store | 1 | 40 |
Create your first PivotTable in Excel
- Select a source-table cell and choose Insert → PivotTable.
- Confirm the source and place the result on a new worksheet.
- Drag Region into Rows.
- Drag Channel into Columns.
- Drag Revenue into Values and confirm Sum of Revenue.
The field areas have different roles. Rows list groups vertically; Columns split them horizontally; Values calculate measures; Filters limit the report’s records. Move fields deliberately instead of treating the first suggested layout as the only answer.
Worked result: reconcile the cross-tab
| Region | Online | Store | Total |
|---|---|---|---|
| East | 180 | 200 | 380 |
| West | 150 | 80 | 230 |
| North | 0 | 40 | 40 |
| Total | 330 | 320 | 650 |
A PivotTable may display an empty intersection rather than 0, depending on its settings. The source still contains no North Online order. Check East Online against S101 and S105: 120 + 60 = 180. The grand total is 650.
Sum, Count and Average answer different questions
Sum of Revenue is an amount; Count of Revenue is a number of populated entries. If Excel initially chooses Count, inspect whether the source column contains text or mixed values, then choose the intended aggregation in Value Field Settings and fix the data type.
An average of order-level prices is not necessarily a unit-weighted average. Define numerator, denominator and source grain before choosing Average. Apply number formatting through the value-field settings where available so the displayed measure stays readable as the PivotTable changes.
Refresh and test the report you built
PivotTables can retain old summarized data until refreshed. After changing the source, use the PivotTable’s Refresh command and verify the source includes new records. A fixed range does not automatically expand just because a record sits underneath it.
Try changing S101 revenue from 120 to 140. After refresh, East Online should be 200 and the grand total 670. The browser exercises test field choices and reconciliation; create and refresh the native PivotTable in Excel. Core PivotTables are available in current web and desktop Excel, with differing feature sets.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 5 exercises complete
The bounded practice checker accepts equivalent supported formulas, lowercase function names and text labels, extra spaces, and comma or semicolon separators. Array tasks check the complete result shape and values. It is a teaching tool, not a complete Excel engine. See supported behavior.
Take this with you
A useful PivotTable depends on tidy input, the correct aggregation, source coverage and refresh. Reconcile one group and the grand total.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Reviewed 2026-10-16.