Data → Answers

Teach yourself PivotTables — well enough to teach them.

🏑 Training log 🗣️ Practice log
§ Intro

How this deck works

Driving it

Click (or → / Space) — build the current slide one step at a time, then move on
← — undo one step, then one slide
E — edit any text in place (Ctrl+S saves a copy)
Rail (left) — jump between the 7 sections

The colour key — memorise this

Amber — the fields we group and slice by
Green — the numbers being summarised
Blue — the computed result
Try it now — click anywhere to light up a cell ▸
One click = one build step. That's the whole trick.
§ Intro

Prerequisite knowledge

✔ Excel tables

Insert ▸ Table  →  Sessions[Hours]

This deck builds every pivot on an Excel Table. You'll need to know how to turn a range into a Table and what a structured reference looks like — if that's unfamiliar, pause here, look it up, and come back.

✔ Basic aggregation

SUM  ·  COUNT  ·  AVERAGE

You'll need to know what these three do to a column of numbers. Nothing beyond that — no lookup functions are required for this deck.

§ Intro

Meet your two datasets

Both are logs: one row per event, growing downwards forever. That shape is exactly what PivotTables are for — and if a technique works on both, it's genuinely understood.

🏑 tblSessions — a training log

12 rows · who trained, where, what kind of session, how long.

🗣️ tblPractice — a language practice log

10 rows · which language was practised, what category, for how many minutes.

§ Why move on from formulas

Twelve rows, one simple question

"Total training hours by state?" The log holds the answer, but no single cell contains it. The formula way works — watch what it costs.

The question: hours by state — five answers wanted, one per state.
=SUMIFS(Sessions[Hours], Sessions[State], "NT") → 8.5
=SUMIFS(Sessions[Hours], Sessions[State], "WA")
=SUMIFS(Sessions[Hours], Sessions[State], "NSW")
=SUMIFS(Sessions[Hours], Sessions[State], "QLD")
=SUMIFS(Sessions[Hours], Sessions[State], "SA")
Five formulas, five hand-typed criteria, a layout you built yourself — and the moment someone asks "…by session type instead?", you start again from scratch.

(first 8 of 12 rows shown)

§ Why move on from formulas

The five pains of summarising by hand

None of these are your fault — they're what happens when the summary is hand-built instead of generated.

①

One formula per answer

A 5-state × 3-session breakdown is fifteen SUMIFS. Every cell is another chance to slip.

→ PivotTable fixes this
②

Data grows, summary doesn't

New rows land below your range and silently miss the totals — unless you re-point every formula.

→ PivotTable fixes this
③

Layout by hand

The row labels, the column headers, the grand total — you type and align all of it yourself.

→ PivotTable fixes this
④

Typos look like answers

Misspell "NT" in a criterion and you get a confident, wrong zero.

→ PivotTable fixes this
⑤

New question = rebuild

"By state" becomes "by month" and the whole sheet of formulas is landfill.

→ PivotTable fixes this
§ PivotTable anatomy

The four areas — the whole machine

Insert ▸ PivotTable gives you an empty grid and this panel. Everything a pivot ever does is dragging field names into four boxes.

Filters

Narrow the whole report to the rows you care about.

Columns

Slice across — one column per value of this field.

Rows

Group by — one row per value of this field.

Values

Do the maths — sum, count or average a number for every group.

Rows — drag State here and the grid grows one row per state Columns — drag Session here and each type gets its own column Values — drag Hours here and every intersection gets a subtotal Filters — optional; the other three do the real work
§ PivotTable anatomy

The sentence you'll teach

Every pivot — no matter how elaborate — is these three plain-English chunks:

group by this
Rows
slice by that
Columns
do maths on those
Values
Say it out loud when you teach: "group by this, slice by that, do maths on those." Filters just narrow the stage first.
§ PivotTable anatomy

First build: hours by state

The SUMIFS question from before — answered by two drags and zero typing. Watch the field list on the left feed the well, and the well shape the result.

PivotTable fields
DatePlayer StateSession Hours
Filters
Columns
Rows
State
Values
Sum of Hours
Row Labels
Sum of Hours
NSW
3
NT
8.5
QLD
6
SA
5
WA
3.5
Grand Total
26
drag State → Rows — the grid instantly grows one row per state, alphabetical drag Hours → Values — every state gets its subtotal, no formulas typed the grand total arrives free — 12 rows became 5 answers in two drags
§ PivotTable anatomy

"Sum of Hours" is a choice, not a fact

Click any value pill and open Value Field Settings — the same drag can answer four different questions. Watch the numbers change while the layout stays put.

Row Labels
Sum of Hours
NSW
3
NT
8.5
QLD
6
SA
5
WA
3.5
Grand Total
26
VALUE FIELD SETTINGS
Sum
Count
Average
Max
Same fields, same drags — only the maths changes.
Count — "how many sessions per state?"
Average — "how long is a typical session?"
Max — "longest single session per state?"
Sum is just the default Excel picked because Hours is numeric Count of Hours: NT trained four times — the values are now row counts, total 12 Average: QLD's sessions are long (3 h); NSW's are short (1.5 h) Max: the single longest session in each state
§ Reshaping

One more drag: a two-way breakdown

Remember pain ① — fifteen SUMIFS for a state × session grid? Here it is for the cost of dragging Session into Columns.

Filters
Columns
Session
Rows
State
Values
Sum of Hours
Sum of Hours
Fitness
Match
Skills
Grand Total
NSW
1
2
3
NT
1
3
4.5
8.5
QLD
6
6
SA
3
2
5
WA
1.5
2
3.5
Grand Total
3.5
12
10.5
26
drag Session → Columns — one column per session type appears every intersection fills with its own subtotal — that's the fifteen SUMIFS, free totals on both axes — the full report, from three drags in total
§ Reshaping

Swap the fields — that's why it's called a pivot

The same report turned on its side: drag State to Columns and Session to Rows. Nothing recalculates from scratch — the summary just re-shapes around your question.

Filters
Columns
State
Rows
Session
Values
Sum of Hours
Sum of Hours
NSW
NT
QLD
SA
WA
Grand Total
Fitness
1
1
1.5
3.5
Match
3
6
3
12
Skills
2
4.5
2
2
10.5
Grand Total
3
8.5
6
5
3.5
26
the two amber fields trade places in the well… …and the whole report turns on its side. Same numbers, new angle — rebuilding costs nothing
§ Reshaping

The fourth box: Filters

Drag Player into Filters and the whole report narrows to whoever you pick — here, Talia Rourke. Every number recomputes from just her rows.

Filters
Player
Columns
Session
Rows
State
Values
Sum of Hours
Player
Talia Rourke ▼
Sum of Hours
Fitness
Match
Skills
Grand Total
NSW
1
2
3
NT
1
3
4.5
8.5
…
3.5
12
10.5
26

(matrix abbreviated — QLD, SA and WA rows behave the same way)

drag Player → Filters — a picker row appears above the report choose Talia Rourke from the dropdown… …and the report shrinks to her rows only: all NT, 8.5 hours. Slicers are this same idea as friendly clickable buttons.
§ Reshaping

Read the gaps: a blank is not a zero

QLD's Fitness cell is empty. That doesn't mean "zero hours of fitness training happened" as a measured fact — it means no rows in the log matched QLD + Fitness at all. Same arithmetic, different story: nothing was recorded.

Sum of Hours
Fitness
Match
Skills
Grand Total
NSW
1
2
3
NT
1
3
4.5
8.5
QLD
6
6
SA
3
2
5
WA
1.5
2
3.5
When you present a pivot, say it the honest way: "no recorded sessions", not "zero hours". If a true 0 matters, put a 0 in the source data — the pivot will happily sum it.
§ Values

The Count trap

Values does Sum for numbers and Count for everything else. That rule is helpful right up until it silently counts something you meant to add up.

Filters
Columns
Rows
State
Values
Count of Player
Row Labels
Count of Player
NSW
2
NT
4
QLD
2
SA
2
WA
2
Grand Total
12
Drag Player (text) into Values → you get Count. Names can't be summed, so Excel counts rows instead — often exactly what you want.
The sneaky version: numbers stored as text also arrive as Count. If a field you know is numeric shows "Count of…", stop — fix the source column (Text to Columns, or re-enter as numbers), refresh, then re-drag.
§ Values

Same numbers as shares: % of Grand Total

Right-click any value ▸ Show Values As ▸ % of Grand Total. The underlying sums don't change — only how they're expressed. "NT does a third of all training" lands harder than "8.5 hours".

Row Labels
Sum of Hours
NSW
3
NT
8.5
QLD
6
SA
5
WA
3.5
Grand Total
26
SHOW VALUES AS
No Calculation
% of Grand Total
% of Row Total
Running Total In…
NT carries 32.7% of all training hours — one right-click turned totals into a story.
§ Values

Values holds more than one field

Drop a second field into Values and each group reports both. Hours and session counts, side by side — this is where a pivot starts looking like a finished report.

Filters
Columns
Rows
State
Values
Sum of HoursCount of Session
Row Labels
Sum of Hours
Count of Session
NSW
3
2
NT
8.5
4
QLD
6
2
SA
5
2
WA
3.5
2
Grand Total
26
12
Those "Sum of / Count of" headers are just labels — click one and type over it: "Hours", "Sessions". Small polish, big readability. (One rule: your label can't exactly match a field name — "Hours " with a trailing space is the classic workaround.)
a second value pill → a second column of maths, computed per group like the first rename the headers and this is a report you could hand to anyone
§ Second dataset — language

Context switch: the practice log

Same skill, new world. tblPractice logs language study: one row per sitting, with the language, a category, minutes spent, and new words learned. If the four-areas sentence works here too, it's truly yours.

Language and Category are the fields you'll group and slice by…
…Minutes and NewWords are the numbers Values will do maths on.
§ Second dataset — language

Say the sentence, make the drags

"Group by Language, no slicing, do maths on Minutes." Two drags — same rhythm as the hockey build, different world.

PivotTable fields
DateLanguage CategoryMinutes NewWords
Filters
Columns
Rows
Language
Values
Sum of Minutes
Row Labels
Sum of Minutes
Arabic
60
Chinese
75
Russian
60
Grand Total
195
Language → Rows: three groups appear Minutes → Values: 195 minutes of study, split three ways Chinese is out in front — and this report updates itself as the log grows
§ Second dataset — language

Slice it: language × category

Add Category to Columns and the study pattern appears: Greetings dominate everywhere, and only Chinese has touched Numbers.

Filters
Columns
Category
Rows
Language
Values
Sum of Minutes
Sum of Minutes
Food
Greetings
Numbers
Grand Total
Arabic
30
30
60
Chinese
25
35
15
75
Russian
15
25
20
60
Grand Total
70
90
35
195
Category → Columns — Food, Greetings, Numbers each get a column the blank says it honestly: no Arabic Numbers practice has been logged yet
§ Second dataset — language

Dates group themselves

Drag Date into Rows and every single day gets its own row — too granular. Right-click any date ▸ Group ▸ Months, and the calendar folds up for you. No helper columns, no MONTH() formulas.

before — one row per date

Row Labels
Sum of Minutes
2026-06-01
35
2026-06-02
25
2026-06-03
50
2026-06-04
15
2026-06-05
10
2026-07-02…
60
GROUP
Days
Months
Quarters
Years

after — grouped by month

Row Labels
Sum of Minutes
Jun
135
Jul
60
Grand Total
195
right-click a date ▸ Group ▸ pick Months (or Quarters, or Years — or several at once) June's push, July's slowdown — visible the moment the dates fold up
§ Maintain + teach

The #1 gotcha: a pivot is a snapshot

A pivot does not watch the source data. Edit the log and the pivot sits there, confidently showing yesterday's answer, until you tell it to look again.

the source — Talia's match gets corrected

the pivot — hours by state

Row Labels
Sum of Hours
NSW
3
NT
8.5
QLD
6
SA
5
WA
3.5
Grand Total
26
The source now says 4 — the pivot still says 8.5. Nothing is broken; it just hasn't looked.
Right-click the pivot ▸ Refresh (or Data ▸ Refresh All). Make it a reflex: edit source → refresh → then read.
§ Maintain + teach

Always build on a Table, never a range

A pivot built on A1:E13 will never see row 14. A pivot built on tblSessions inherits every new row automatically — refresh, and they're in the numbers.

New session logged on the next empty row. To a fixed range, this row doesn't exist — it will be silently missing from every total, forever.
A Table auto-expands to swallow the new row, and the next Refresh carries it into the pivot. This is why the prerequisite slide insisted on Tables.
§ Maintain + teach

Three finishing touches

Raw pivots read like machine output. Three thirty-second fixes turn one into a report: rename the value header, format the numbers, sort by the value.

Row Labels
Sum of Hours
NSW
3
NT
8.5
QLD
6
SA
5
WA
3.5
Grand Total
26
1 · Rename — click the header, type "Hours " (trailing space beats the name-clash rule)
2 · Format — Value Field Settings ▸ Number Format ▸ one decimal place, applied everywhere at once
3 · Sort — right-click a value ▸ Sort ▸ Largest to Smallest; the biggest story moves to the top
§ Maintain + teach

The cheat sheet — screenshot this one 📸

Rowsgroup by this Columnsslice by that Valuesdo maths on those Filtersnarrow the whole report (optional)

Which box does it go in?

A"breakdown by X" → X to Rows
B"compare across Y" → Y to Columns
C"total / count / average N" → N to Values
D"only for Z…" → Z to Filters or a Slicer

Values can be…

ΣSum (default for numbers)
#Count (default for text)
x̄Average, Max, Min…
%Show Values As ▸ % of Grand Total

Remember

  • Build on a Table, not a range
  • Edited the source? Refresh
  • "Count of" a number = text in disguise
  • Blank cell = no matching rows, not zero
  • Right-click dates ▸ Group ▸ Months
  • Never type into a pivot — fix the source
fields you group / slice by
numbers being summarised
computed result
Insert ▸ PivotTable · any modern Excel
§ Maintain + teach

When it goes wrong — the usual suspects

Stale numbers

The source changed; the pivot didn't.

Fix: right-click ▸ Refresh. Every time. It's never automatic.

"Count of Hours"

You wanted Sum but a numeric field arrived as Count — the column holds text-formatted numbers (or blanks).

Fix: clean the source column to real numbers, refresh, re-drag.

Missing rows

New data sits below a fixed-range source and never joins the totals.

Fix: rebuild the pivot on a Table (Insert ▸ Table first).

Can't create the pivot

Merged cells or blank header cells in the source stop the dialog cold.

Fix: unmerge everything; give every column a one-cell header.

Edited a pivot cell

You typed over a value and Excel refused — pivot output is read-only.

Fix: that's by design. Change the source data, then refresh.

Golden rule

The pivot is a view, the Table is the truth. Every fix above is some version of: repair the source, then refresh the view.

§ Maintain + teach

Now teach it

Your six talking points

  1. The sentence: group by this · slice by that · do maths on those. (Filters narrow the stage first.)
  2. Four boxes are the whole machine — Rows, Columns, Values, Filters. Text fields group; number fields calculate.
  3. Sum is a choice — Value Field Settings switches it to Count, Average, Max, or % of total.
  4. A pivot is a snapshot — edit the source, then Refresh. It never updates itself.
  5. Blank ≠ zero — a gap means no rows matched, and "Count of" a number means text in disguise.
  6. Build on Tables — new rows then flow in on every refresh.

Three challenges — click to reveal each answer

① Hockey · Total hours by session type?
Session → Rows · Hours → Values → Fitness 3.5 · Match 12 · Skills 10.5
② Language · Each language's share of total study minutes?
Language → Rows · Minutes → Values ▸ Show Values As ▸ % of Grand Total → Arabic 30.8% · Chinese 38.5% · Russian 30.8%
③ Combined · Talia's hours, broken down by session type?
Session → Rows · Hours → Values · Player → Filters, pick Talia → Fitness 1 · Match 3 · Skills 4.5

If you can walk someone through these three, you own it.

editing — click any text · Ctrl+S saves a copy · E to exit