How a PivotTable groups rows

The idea

A PivotTable does one thing: it sorts your rows into buckets and summarises each bucket. You choose the field that decides the bucket, and it builds the table.

Say the source has one row per order line, and you put category in Rows. Excel looks down the category column, finds the distinct values (Decor, Furniture, Kitchen and so on), and opens one bucket for each. Every source row goes into the bucket that matches its category. Then, for each bucket, it applies the summary you chose for Values: add up revenue, or count the lines.

That is the same job as GROUP BY in SQL or groupby in pandas, with one difference: you rebuild the table by dragging a field instead of rewriting a query.

Why it exists

In chapter 3 you answered "revenue per category" with SUMIFS: one formula per category, written once and filled down. That works, but you had to type the list of categories yourself. If a seventh category appeared in the data, your table would silently miss it.

A PivotTable builds the list of buckets from the data, so nothing is left out and nothing is typed by hand. It also lets you change the question in seconds. Swap category for channel and you have revenue per channel. Drag channel into Columns as well and you get a grid of category by channel. With formulas, each of those is a new table to build; with a PivotTable it is one drag.

How it works under the hood

Four boxes in the PivotTable Fields pane decide the result. In Excel you find them after Insert → PivotTable, with the fields listed above the boxes.

• Rows: one bucket per distinct value, listed down the side.
• Columns: one bucket per distinct value, listed across the top. A field in both Rows and Columns makes a grid, and each cell is the bucket for that pair of values.
• Values: the numbers summarised inside each bucket.
• Filter: rows that never enter any bucket. A filter on channel = web removes the other lines before the grouping happens.

The Grand Total at the bottom is not a sum of the displayed numbers. It is the summary run over all rows, with no bucket. For Sum the two are the same, and that is a good check: the buckets must add up to the total.

Two fields make a grid

Put channel in Columns and keep category in Rows. Now every line is sorted twice: by its category, which picks the row, and by its channel, which picks the column. The cell where Outdoor meets web holds only the Outdoor lines sold on the web. Cells where no line fits stay empty, and the row and column totals are the buckets you already had. Two fields is the limit for reading comfortably; with a third you are usually better off with a filter.

Sum, Count and the other summaries

The summary decides what each bucket tells you. Sum adds up the amounts. Count says how many lines are in the bucket. Average divides the two. The same buckets, three different answers.

Excel picks Count for you in one case you will meet often: when the field in Values contains text, or numbers stored as text. If a column of amounts shows "Count of revenue" instead of "Sum of revenue", the amounts are probably text. Fix the column first (chapter 5), then change the summary.

"Show values as" does not change the buckets, only how each bucket's number is displayed. % of Grand Total divides by the total, % of Column Total divides by the column. Use it when the reader needs shares more than amounts.

The snapshot

A PivotTable does not look at your source cells each time. When you build it, Excel stores a copy of the source, the pivot cache, and draws from that. This is why a pivot on a big table opens fast. It is also why the numbers can be old.

After you edit, add or delete source rows, refresh the PivotTable. If you add rows below the range, the new rows are outside the source and a refresh will not find them. Turn the source into a Table (chapter 6) and the range grows by itself.

Common misconceptions

"A PivotTable updates when I change the data"

A formula does. A PivotTable does not, until you refresh it. If a pivot total looks wrong after you fixed a figure in the source, refresh before you suspect anything else. A good habit is to compare the Grand Total with a plain SUM of the source column; if they differ, the pivot is stale or the source range stops short.

"Count of revenue tells me how many orders there are"

Count counts rows in the bucket, and a row here is an order line, not an order. Order 9938 has three lines, so it adds three to the count. To count orders you need a field with one row per order, or a distinct count. Check by comparing the count with what you can see: the twelve lines above come from only eight orders.

"Sum is always the right summary for a number"

Sum is right for amounts you can add: revenue, units. It is wrong for a price or a rate, because adding three unit prices gives a number that means nothing. Ask what you want each bucket to say. The average price of the lines is a fine answer; the sum of the prices is not.