Level 7 · Boss: a day's sales, from lines to a pivot to the headline

One day, three layers

A useful sales note has three layers: the lines (what happened), a summary (where the money went) and a headline (what to say first). This boss builds all three on Monday, May 12, 2025: 39 order lines in A1:F40, completed, cancelled and refunded mixed together.

Step 1: a field to add up

The export has quantity, price and discount but no revenue. A PivotTable can only summarise a field that exists, so first add one:

G1   line_total
G2   =D2*E2*(1-F2/100)

Filled down to G40. With a header and no gap, column G becomes part of the pivot’s source, just as it would in Excel if the data were a Table (Ctrl+T). In Excel you could also add a calculated field instead, but a helper column is visible and easy to check.

Step 2: completed revenue by category

Status belongs in Filter: the question is about completed sales only, not a split by status.

Filter    status = completed
Rows      category
Values    Sum of line_total

status        completed

category      Sum of line_total
Decor               626.27
Kitchen             149.91
Lighting            207.96
Outdoor           5,419.06
Textiles            493.02
Grand Total       6,896.22

Without the filter the Grand Total would be 7,398.12: the six cancelled lines (396.91) and one refunded line (104.99) would count as sales.

Step 3: the headline, read from the pivot

The pivot’s values are ordinary cells, so formulas can read them. Keep the rows in A→Z order and let the formulas find the winner, so the headline doesn’t depend on how the pivot is sorted:

M2   =LARGE(J4:J8,1)                          biggest category revenue
M1   =INDEX(I4:I8,MATCH(M2,J4:J8,0))          the label next to it
M3   =M2/J9                                   share of the Grand Total
M4   =INDEX(I4:I8,MATCH(LARGE(J4:J8,2),J4:J8,0))

LARGE(range, k) returns the k-th largest value, so k = 2 is the runner-up. MATCH(…, 0) finds its position and INDEX returns the category in the same position, the same pair you met in chapter 2.

In Excel, clicking a pivot cell while typing a formula inserts a GETPIVOTDATA(…) call instead of J4. It reads a value by field name, so it survives a re-sorted pivot; plain references like J4:J8 are easier to read and are what this sheet supports. You can switch the habit off under PivotTable Analyze → Options → Generate GetPivotData.

Thinking like an analyst

Outdoor at 78.6% of a Monday in May is the start of patio season, not a one-off: the next ticket would compare the same weekday across April and June. And the 501.90 of cancelled or refunded lines is worth one sentence in the note, because it is the difference between the till and the sales report.

Common mistakes

• Putting status in Rows instead of Filter: the table splits into three lines per category.
• A helper column without a header, which the pivot can’t see.
• Typing 5,419.06 into M2: the headline goes stale the moment the pivot changes.
• MATCH without the 0: an approximate match on unsorted numbers returns the wrong category.