How do pivot tables work? The four drop zones that replace 40 SUMIFS

Stop writing 40 SUMIFS. A pivot table is four drag-and-drops that answer the same question, refreshably. One page on the anatomy, the hidden settings, and the cache trap that ships wrong numbers. The print-ready A4 PDF is at the bottom.

The four zones

• Rows: what you group by.
• Columns: the second group-by.
• Values: the number, aggregated.
• Filters: the slice on top.

Values

• Sum by default, for numeric columns.
• Count is what any text-polluted column gets, silently.
• Right-click a value → Summarize By → average, max, distinct count. Nobody finds it alone.

Show values as

• % of column total: shares instead of raw sums.
• Running total: cumulative in one click.
• Difference from previous: month-over-month without a single formula.

The source

• One flat table, headers on exactly one row.
• No merged cells: they break grouping.
• Ctrl+T first: a real table auto-grows, so new rows join the pivot on refresh.

The same pivot in pandas and SQL

pd.pivot_table(sales,
    index="region",      # rows
    columns="quarter",   # columns
    values="amount",     # values
    aggfunc="sum")

# the SQL twin:
# SELECT region, quarter, SUM(amount)
# FROM sales GROUP BY region, quarter

A pivot table IS a GROUP BY. Explaining one in terms of the other is the cheapest way to prove you understand both.

The trap: the pivot that reports wrong numbers

Power moves

• Group dates: right-click → group by months, quarters, years.
• Double-click any number: the raw rows behind it appear on a new sheet.
• Slicers: filters your stakeholders can click without breaking anything.

Interview phrasing worth memorizing: a pivot is a snapshot of its cache, not a live view. Refresh is part of the workflow, not an afterthought.