Pivot Tables for Analysts: From Raw Rows to a Useful Answer
Use a pivot table to answer a real sales question: prepare the rows, check the grain and totals, avoid counting lines as orders, then add a simple chart.
7 min readBranislav Mateas
Copy post as Markdown
On this page
A pivot table can turn a long export into an answer in about a minute. It can also give you a tidy, wrong answer in the same minute, because it summarises whatever rows it receives. It has no idea that a row is an order line rather than an order.
This guide takes one question from raw rows to a chart, with a check at each step: revenue fell this week, so where did the drop come from?
The data is invented, and the totals were worked out by hand rather than taken from a real shop. Menu names can vary between Excel versions and Google Sheets, so treat them as signposts.
Check the rows before you pivot
Here is the hypothetical export. Each row is one line of an order, so one order can take several rows.
Microsoft's PivotTable guide sets out the requirements: one row of unique, non-blank headers, no blank rows or columns, and one data type per column. It also recommends using an Excel table, so that new rows are included when you refresh. Google Sheets also needs a header on each column.
Before you insert anything, write down two facts:
The grain: one row is one order line, not one order.
The control total:=SUM() on the revenue column gives 400, across 8 rows and 6 orders.
Every pivot you build from this data must return to 400. That check takes seconds, and it will catch most of the mistakes below.
Build the table from the question
Insert the pivot table on a new sheet, then place only the fields that the question needs. Put Channel in Rows, Week in Columns and Revenue in Values.
Channel
W1
W2
Change
Organic
100
80
-20
Paid
150
70
-80
Total
250
150
-100
The Change column is a plain formula beside the pivot, or a calculated field if your tool offers one. A formula beside a pivot can break if the pivot changes shape, so check it after you edit the layout.
The grand total is 400, so the table matches the control total. The answer has also started to appear: Paid accounts for 80 of the 100 drop.
Now check how the value is being summarised. Excel defaults to a sum for numbers, but Microsoft notes that if it interprets the data as text, the field is displayed as a COUNT. If one export brings revenue in as text, you may see row counts in a column labelled like revenue. Again, the control total exposes it at once, since the total would read 8 rather than 400.
Counting lines is not counting orders
The next question is obvious: did Paid lose orders, or did its orders get smaller? Here is where the grain matters. Add Order to Values as a count, and the pivot counts rows.
Channel
W1 rows
W2 rows
W1 true orders
W2 true orders
Organic
2
2
1
1
Paid
2
2
2
2
On rows alone, the total is 8, and average order value appears to be 400 / 8 = 50. The real figure is 400 / 6, or about 66.67.
There are two common fixes. Some tools offer a distinct count. In Excel, community answers on Microsoft Q&A tie that option to adding the data to the Data Model, which can change other pivot features, so check it in your own version.
The other fix works almost anywhere. Add a helper column to the source data, marking the first row of each order:
=IF(COUNTIF($A$2:A2,A2)=1,1,0)
Sum that column in the pivot, and you get orders rather than lines: 1, 0, 1, 1, 1, 0, 1, 1, which is 6. There is one limit. The helper credits each order to its first row. That is safe when every line of an order shares the same week and channel, as here. It is not safe by Category, because order 1001 has two categories and would appear only under Shoes.
With true orders, the picture becomes clearer. Paid had 2 orders in both weeks, and its average order value fell from 75 to 35. So the question has changed. Paid did not lose orders in this example. It lost order value.
Drill down, then show shares carefully
Add Category as a second row field beneath Channel, or filter to Paid and put Category in Rows.
Paid category
W1
W2
Change
Bags
60
0
-60
Shoes
90
40
-50
Socks
0
30
+30
Total
150
70
-80
The total of -80 agrees with the earlier table. With two orders a week, this is an illustration, not evidence. Real data would need more volume before you drew a conclusion from it, and a second look at the prices, campaigns and products behind the change.
Percentages can help here, as both Excel and Google Sheets can show values as a share of a whole instead of a raw number. Be clear about which whole. Paid is 220 of 400, or 55%, of the two-week total. But Paid's share of W2 alone is 70 of 150, or about 47%. Both are correct, and they answer different questions.
Watch filters as well. A filtered pivot has a smaller total, so it will no longer match your control total. That is expected, but the filter should be visible to anyone reading the result. Otherwise, they may take a partial total for the whole.
Averages need the same care. To get average order value, divide total revenue by true orders. An average of the Revenue column gives average line value, which is another metric.
Add a chart only after the table is right
A chart built on a pivot inherits its logic and its mistakes, so it comes last. In Excel, you can insert a PivotChart, and Microsoft notes that filtering the PivotTable also filters the chart. This is useful, and it is also a risk if someone leaves a filter on.
For this question, a simple clustered column chart with Channel on the axis and one series per week is enough. It shows the Paid drop at a glance. Give it a title that states the finding, such as "Paid accounts for most of this week's revenue drop", and mention the period.
Before you share it
Run this short routine each time:
Refresh. In Excel, a PivotTable needs refreshing when its source changes.
Reconcile. Remove the filters and compare the grand total with the control total.
Check the range. Make sure the new rows are inside the source range.
Test by hand. Add up one cell, such as Paid in W2, from the raw rows: 40 + 30 = 70.
Name the grain. Say whether each number counts rows, orders or something else.
If the raw rows need a join or the file repeats every week, a pivot may no longer be the right place for that logic. The same reasoning appears in SQL aggregations: before you trust a total, say what one row means.
