Ask a room of office workers to raise a hand if they use PivotTables and you’ll get 20%. Ask who wishes they did and you’ll get the rest. The feature has a reputation: powerful, advanced, “I tried once and it made something weird.”

Here’s the secret: if you’ve ever written a SUMIFS — or even just wanted one — you already understand PivotTables. A PivotTable is a whole grid of SUMIFS questions, answered at once, by dragging.

The mental model

In the SUMIFS guide the pattern was: add up this column, where that column matches this condition. One formula, one answer.

A PivotTable asks the same thing in bulk: add up Amount, for every Region crossed with every Product. Rows become one side of the question, Columns the other, Values the thing being added — and Excel fills in the entire grid.

The list (one row = one sale) Region Product Amount Cork Pro 480 Dublin Lite 220 Cork Lite 150 Dublin Pro 610 …2,000 more rows pivot The answer grid Sum Lite Pro Cork 150 480 Dublin 220 610 Total 370 1,090 every cell = one SUMIFS, answered by dragging
Rows in, answers out: a PivotTable is a grid of SUMIFS you didn't have to write.

Build your first one, calmly

  1. Start from a Table (always a Table — the Pivot then follows the data as it grows). Insert → PivotTable → OK.
  2. You get a blank canvas and a field list. Nothing weird has happened; it’s simply waiting for the question.
  3. Drag Region into Rows. Drag Amount into Values. You’ve just asked “total by region” — the answer’s already there.
  4. Drag Product into Columns. Now it’s region × product.
  5. Drag Region out and Month in. Different question, same two seconds. That’s why it’s called a pivot.

The whole skill is learning to hear questions as drags: “by” = Rows, “split across” = Columns, “how much/many” = Values.

The four settings that fix “it made something weird”

  • It’s counting when you want sums. If Amount arrives as a Count, a text value or blank is hiding in the column — fix the data, or right-click → Summarise Values By → Sum.
  • The layout looks like a staircase. Design → Report Layout → Show in Tabular Form + Repeat All Item Labels. This one change makes Pivots look like reports instead of puzzles.
  • Stale numbers. Pivots snapshot the data; right-click → Refresh after edits. (New rows are why the source must be a Table.)
  • Ugly date grouping. Right-click any date → Group → Months and/or Years. Ungroup the same way — nothing is ruined, ever.

That last point generalises: you cannot break the data from inside a PivotTable. It’s a lens, not an editor. Drag anything anywhere; worst case, drag it back out.

When the Pivot earns its keep

The honest heuristic: one question, once → write the SUMIFS. Many related questions, or the same question every month → build the Pivot, add a Slicer (Insert → Slicer) so the question can be changed by clicking, and you’re most of the way to a dashboard — which is exactly where the path goes next.