Level 2 · Count orders by channel and status

The first PivotTable grouped in one direction. Most questions have two: “orders per channel, split by status”. A PivotTable puts the second field across the top, and every cell where a row and a column cross holds one number.

Columns make a cross-tab

The sheet holds the 35 orders placed on Sunday, December 21, in A1:E36. Build the PivotTable with:

Box	Field
Rows	channel
Columns	status
Values	order_id, summarised by Count

The result at H1:

Count of order_id   status
channel        cancelled   completed   refunded   Grand Total
app                               13                    13
marketplace                        5                     5
web                    5          11          1         17
Grand Total            5          29          1         35

The first row names the summary and the column field; the second row lists the column items, sorted A→Z, then Grand Total. Each row has its own Grand Total on the right, and the last row adds up each column.

Count, not Sum

To count records, put any field that is filled on every record into Values and switch “Summarise values by” to Count. order_id is the safest choice: every order has one. Count counts non-empty cells, so a field with gaps (a coupon code, say) would undercount.

Excel picks the default for you: Sum when the field holds numbers, Count when it holds text. Here order_id is a number, so Excel would start with Sum of order_id. Always read the value header before you read the numbers.

Empty cells mean “no records”

app has no cancelled orders, so that cell is empty, not 0. A PivotTable only fills a cell when at least one record lands in it. If you want zeros, Excel has a setting for it (PivotTable Options → “For empty cells show: 0”). Leave them empty here; the checker expects exactly what a default PivotTable shows.

Reading it

Web took 17 orders and 5 of them were cancelled, while app and marketplace had none. That is 5 of the day’s 35 orders, all on one channel. One more refund on the web makes it 6 problem orders on the website alone. A pattern that sharp is worth a message to engineering.

Common mistakes

• Sum of order_id. Adding ids together gives a big number that means nothing.
• Counting a field with gaps. Count of coupon_code counts only the orders that used a coupon. Use a field that every record has.
• Swapping Rows and Columns. The numbers stay the same but the table turns sideways. Put the field with more items in Rows, so the table grows downwards and stays readable.
• Reading an empty cell as missing data. It means zero records, not “unknown”.