Build Your First Excel PivotTable and Reconcile the Totals
On this page
A PivotTable summarises records without a separate formula for every group. It is useful for totals by category, but the summary is only trustworthy when the source range, calculation and refresh state are correct.
Practice setup: Start in a new workbook with fictional data. Formulas use English function names and commas; your regional settings may require semicolons. Interface labels vary between Excel versions. Keep the source data and check your results before reusing the method.
Prepare the source
Use Category and Amount in A1:B7. Add Stationery 360, Stationery 250, Home 600, Stationery 320, Home 450 and Tech 800. Convert the list to an Excel Table.
Each row represents one sale. Use complete headers, numeric amounts and no merged cells. Keep a precomputed grand total outside the source records so the PivotTable does not count it as another sale.
Insert and arrange the fields
Click inside the Table, choose Insert and PivotTable, then place it on a new worksheet. Confirm the source includes all six records. In the field list, put Category in Rows and Amount in Values.
Check the value calculation. For this question it should be Sum of Amount, not Count of Amount. If Excel defaults to Count, inspect whether the source amounts contain text or inconsistent entries rather than merely changing the label.
Check the answer
Stationery should total 930, Home 1050 and Tech 800. The grand total is 2780. Compare it with a separate SUM over the source amounts. Matching totals are a useful first check, though they cannot prove every individual record is correct.
If your version allows the same field twice in Values, add Amount again and set the second calculation to Count. Give it a clear caption such as Sale records. Counts should be 3, 2 and 1 for Stationery, Home and Tech.
Know what the count represents
These are record counts, not distinct customers. The source has no customer field, so the PivotTable cannot infer customer identity. Labels should describe what the data actually measures.
Start with one question and a small set of fields. Adding many dimensions can create a confusing report even when every calculation is technically valid. Keep units and the reporting scope visible.
Refresh after editing the source
Change Tech from 800 to 900. The PivotTable may retain the old result until refreshed. Use Refresh and check that Tech becomes 900 and the grand total becomes 2880.
When adding records, confirm the source Table expands or update the fixed range, then refresh. Refreshing an unchanged range that excludes the new row cannot bring that row into the report.
Check filters before diagnosing a mismatch
A filtered PivotTable can correctly differ from the full source total. Inspect active filters and explain the subset. Do not erase filters merely to force a reconciliation without understanding the report's intended scope.
Try it yourself
Restore Tech to 800, add Home 200 to the source Table and refresh. Predict Home's total and count, plus the full total.
Check your answer
Home becomes 1250 across 3 records. The grand total becomes 2980 across 7 records. If Home stays at 1050, inspect source inclusion and refresh status.
Do not type 1250 directly into a report as a substitute for fixing its source. A reusable summary must recalculate from the actual records.
Keep exploring: Excel learning path.