Contact, if you are interested in this website / development / Sponsorship / Advertisement / Partnership →

Pivot tables explained

The fastest way to turn thousands of rows into an answer. Once you see how the four building blocks fit together, you'll use pivots everywhere.

Data.Studio Editorial · Updated November 2026 · 7 min read

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:

RegionQuarterProductRevenue
NorthQ1Laptop12,000
NorthQ2Laptop15,000
SouthQ1Phone7,200
SouthQ2Phone8,000

…a pivot with Rows = Region, Columns = Quarter and Values = Sum of Revenue produces:

RegionQ1Q2Total
North12,00015,00027,000
South7,2008,00015,200
Grand total19,20023,00042,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.

Try it: open the Pivot Table, load the sample, and switch between Sum, Count, Average and Distinct count to see how the answers change.