3.1Pivot tables (TCD)
The essentials in 30 seconds
- Summarize without a formula: you drag fields into four areas — Rows, Columns, Values, Filters — and the pivot table calculates (sum, average, count) in a few seconds.
- The source must be flat: one row = one record, unique headers, no empty rows or mixed-in totals — ideally a structured table that follows new rows.
- A pivot table does not update itself: after each source change, right-click ▸ Refresh — and you change the angle by drag-and-drop, not by redoing everything.
Running mini-case — Wassim replaces 2 hours of SUM with 30 seconds
Wassim, an accountant in Algiers, totals sales by region each month with manual SUMs over 4,000 rows — two hours, and one forgotten row every time. He converts his table into a structured table, inserts a pivot table (Region in Rows, Sum of Amount in Values), adds Product in Columns: the full cross-tab appears in 30 seconds, and each month he refreshes instead of starting over. No more totals recalculated by hand.
The same case returns in the course, the diagram, the script and the 48h challenge.
1. Summarize without a formula: drag fields into four areas
The pivot table (TCD) summarizes a large table by dragging fields into four areas: Rows, Columns, Values (what you calculate: sum, average, count…) and Filters. In a few seconds, without writing a formula, Wassim gets «sales by region and by month». It is Excel's most powerful analysis tool: you describe the angle, Excel aggregates.
The strength of the pivot table is flexibility: you rearrange the fields to change the angle (by product, then by salesperson), you change the aggregate (sum → average), you group dates by month or quarter — all by drag-and-drop. Where a formula freezes a calculation, the pivot table keeps exploration open.
2. Flat source, structured table, refresh: the three reflexes
A good pivot table starts from a «flat» table: one row = one record, unique headers, no empty rows or totals mixed into the data. Ideally, the source is a structured table: the pivot table then automatically follows new rows. Otherwise, a frozen range «forgets» additions — and the figures become wrong without warning.
And above all: a pivot table does not update itself when the source changes — you must Refresh (right-click ▸ Refresh). Wassim made himself a tattooed rule: source modified = immediate refresh. Structured table + systematic refresh: the pivot table stays right, month after month.
🔎 Concrete example — Sales by region in 30 seconds
Wassim's «Sales» source (Date, Region, Product, Amount):
Pivot table fields:
Rows = Region
Values = Sum of Amount
=> total sales by region, without any formula
(Columns = Product => Region x Product matrix)Reading: dragging Region to Rows and Amount to Values is enough.
Lesson: add Product to Columns and you get a full cross-tab — hence «crosstab».
Section vocabulary
- Pivot table
- Pivot table: a table's summary by dragging fields, without a formula.
Simple example: Wassim's pivot table: sales by region in 30 seconds.
What it is not: manual SUMs recalculated each month.
Reflex: summarize = pivot table first, formula next.
- Pivot table areas
- Rows, Columns, Values, Filters: the four slots where you drag the fields.
Simple example: Region in Rows, Amount in Values.
What it is not: columns calculated by hand.
Reflex: one field = one area, one angle = one layout.
- Aggregate (Values field)
- The calculation applied: Sum, Average, Count, Max… — changeable in one click.
Simple example: Wassim switches from Sum to Average for the average basket.
What it is not: an average frozen in a formula.
Reflex: change the aggregate to change the question.
- Refresh
- Reload the pivot table after a source change — never automatic.
Simple example: Wassim refreshes after each monthly addition.
What it is not: a pivot table that would follow the source by itself.
Reflex: source touched = immediate refresh.
- Flat table
- Ideal source: 1 row = 1 record, unique headers, no blanks or mixed-in totals.
Simple example: The Sales table converted into a structured table.
What it is not: a range with empty rows and subtotals.
Reflex: clean the source before any pivot table.
In practice — Creating a first pivot table
- Convert the source into a structured table, then Insert ▸ PivotTable.
- Drag a category to Rows and an amount to Values (Sum).
- Add a 2nd field to Columns to cross two dimensions.
- Modify the source, then Refresh and check the update.
Ready-to-say script — presenting the pivot table to the chief accountant (30 s)
«No more manual SUMs over 4,000 rows: a pivot table summarizes sales by region and by product in 30 seconds, without a formula. The source is a structured table, so each month I add the rows and refresh — the figures stay right.»
To say in a meeting; project the drag-and-drop live.
Step-by-step template — sales by region pivot table
1) Source: Ctrl+T on the table (Date, Region, Product, Amount)
2) Insert > PivotTable > New sheet
3) Rows <- Region
Values <- Amount (Sum of Amount)
Columns <- Product
4) Date in Rows > right-click > Group > Month
5) Source modified? Right-click the pivot table > RefreshFreelancer
A pivot table on a structured table and your monthly summaries take 30 seconds instead of 2 hours.
SME in Algiers
Wassim delivers sales by region each month: structured source, refreshed pivot table, zero formula.
Project team
Impose a structured-table source + refresh before any shared figure.
Beginner
This week: a Region × Product pivot table, then a grouping of dates by month.
✓ Best practices
- Start from a flat table / structured table.
- Remember to Refresh after each source change.
- Change the angle by drag-and-drop rather than redoing.
✗ Common pitfalls
- Source with empty rows / mixed-in totals → wrong pivot table.
- Forgetting to Refresh (outdated figures).
- Basing the pivot table on a frozen range that does not include new rows.
Key takeaways
- The pivot table summarizes a table by drag-and-drop (Rows/Columns/Values/Filters).
- The source must be flat; ideally a structured table.
- A pivot table does not update itself: Refresh.
- You change the angle by rearranging the fields.
48h challenge — apply tomorrow
Replace 1 manual summary with a pivot table on a structured table, and prove the refresh after adding a row.
- My pivot table summarizes by drag-and-drop, without a formula.
- My source is a structured table, without blanks or totals.
- I refresh after each source change.
Frequently asked questions
My pivot table does not show the new rows?
Either the source is a frozen range (convert it into a structured table), or you have not refreshed. Right-click ▸ Refresh; with a structured table, the range extends automatically.
How to group dates by month in a pivot table?
Place the Date field in Rows, right-click ▸ Group, choose Months (and/or Quarters, Years). Excel creates the time hierarchy without a formula.
Does a pivot table update automatically when the source changes?