The idea in one sentence
A pivot table groups rows that share values in one or more columns, then aggregates a numeric column for each group. "Total revenue by region" is a pivot. So is "number of support tickets by agent and week".
The four building blocks
- Rows: the field whose values become row labels (Region).
- Columns: an optional second field spread across the top (Quarter). This creates a cross-tab.
- Values: the field being measured (Revenue).
- Aggregation: how values are combined: sum, count, average, minimum, maximum or distinct count.
Given an orders table like this:
| Region | Quarter | Product | Revenue |
|---|---|---|---|
| North | Q1 | Laptop | 12,000 |
| North | Q2 | Laptop | 15,000 |
| South | Q1 | Phone | 7,200 |
| South | Q2 | Phone | 8,000 |
…a pivot with Rows = Region, Columns = Quarter and Values = Sum of Revenue produces:
| Region | Q1 | Q2 | Total |
|---|---|---|---|
| North | 12,000 | 15,000 | 27,000 |
| South | 7,200 | 8,000 | 15,200 |
| Grand total | 19,200 | 23,000 | 42,200 |
Choosing the right aggregation
- Sum for additive measures: revenue, units, hours.
- Count for "how many records": orders, tickets, sign-ups. It counts non-empty cells, so pick a column that's always filled, like an ID.
- Distinct count for "how many unique": customers, products, visitors. Ten orders from three customers is a count of 10 but a distinct count of 3.
- Average for typical values: order value, response time. Watch out for the trap below.
- Min / Max for extremes and date ranges: first order date, largest deal.
The average-of-averages trap
Suppose Store A has 1,000 orders averaging $20 and Store B has 10 orders averaging $200. The average of the two store averages is $110. The true average order value across all 1,010 orders is about $21.78. A correct pivot computes grand totals from the underlying rows, not by averaging the row results, and so does our Pivot Table tool. If you rebuild totals by hand in a spreadsheet, recompute them from the raw data.
The same applies to rates. Never average conversion rates across segments. Divide total conversions by total visitors.
Practical tips
- Clean categories first. "North" and "north " become two rows. Run the Data Cleaner to trim and standardise.
- Watch blanks. Empty values form their own "(blank)" group. Decide whether that's meaningful or a data gap.
- Sort by the measure to turn a pivot into a ranking. Sort by the label when order is natural (months, sizes).
- Keep the grain in mind. If your table has one row per order line, counting rows counts lines, not orders. Use distinct count on order ID.
- Pivot, then chart. A pivot is often the perfect input for a bar or line chart. Export the CSV and drop it into the Chart Maker.
When to move beyond pivots
Pivots are perfect for ad-hoc questions. If you rebuild the same pivot every week from a fresh export, that's a signal to automate it with a scheduled query and a dashboard, so the answer is always up to date and nobody's Monday morning disappears into copy-paste.