A pivot table is a tool that summarizes a dataset by grouping and aggregating values. It lets you answer questions like "How many records are in each category?" or "What is the total for each region?" without writing formulas — by dragging and dropping fields into a summary layout.
What a Pivot Table Does
A pivot table takes a flat data table and reorganizes it into a summary. You choose:
- Rows — the field whose unique values become row labels (e.g., province)
- Columns — an optional second grouping dimension (e.g., year)
- Values — the field to aggregate (e.g., total sales, count of records)
- Filters — an optional field to restrict which records are included
Why Clean Source Data Matters
A pivot table is only as reliable as the data it summarizes. Common problems that distort pivot table results:
- Inconsistent categories. If "Ontario", "ON", and "Ont." all appear in a province column, the pivot table will treat them as three separate groups instead of one.
- Duplicate records. Duplicate records inflate counts and totals.
- Missing values. Blank cells in the row or column field create an unnamed group in the summary.
- Mixed data types. A column that contains both numbers and text will not aggregate correctly.
Common Aggregations
- Count — how many records fall into each group
- Sum — the total of a numeric field for each group
- Average — the mean of a numeric field for each group
- Min / Max — the smallest or largest value in each group
Rearranging Dimensions
One of the most powerful features of pivot tables is the ability to rearrange the summary layout quickly. You can swap rows and columns, add or remove fields, and change the aggregation type — all without modifying the source data.
Refreshing Summaries
Pivot tables do not update automatically when the source data changes. After adding, editing, or deleting records in the source table, you must refresh the pivot table to reflect the changes. Also verify that the source range still covers all your data — if you have added rows beyond the original range, the pivot table may not include them.
Validating Results
Always validate pivot table totals against the source data. A grand total in a pivot table should match a SUM formula applied to the same field in the source table. If they differ, investigate before drawing conclusions.
Common Mistakes
- Forgetting to refresh after source data changes
- Source range not covering all data rows
- Inconsistent category values creating spurious groups
- Treating pivot table output as a new authoritative dataset rather than a summary view
Key Takeaways
- Pivot tables summarize flat data by grouping and aggregating values.
- Clean, consistent source data is essential for reliable summaries.
- Refresh the pivot table after source data changes.
- Validate totals against the source data before drawing conclusions.
- pivot table tutorial
- spreadsheet pivot table
- data summary spreadsheet