Pivot tables in 5 minutes

A pivot table summarizes a long list into a short table. You tell it which field to group by and what to calculate, and Excel does the rest. It's the same as a SQL GROUP BY, but with the mouse.

The three decisions

• •Rows: the field you group by (store, month, product…).
• •Values: the field you calculate (amount, units…).
• •Operation: sum, count, average, maximum or minimum.

“Sales by store” is Rows = Store, Values = Amount, Operation = Sum. “How many orders did each customer place” is Rows = Customer with the Count operation.

Before you create it

• •Every column needs a header, with no merged cells.
• •One row = one record. No subtotals mixed in with the data.
• •Numeric columns have to be real numbers, not text.

🦉 Tip: If a total looks odd, switch the operation to Count: you'll see how many rows are in each group and find the problem sooner.

-- The same idea in SQL
SELECT store, SUM(amount)
FROM sales
GROUP BY store;