Level 4 · A PivotTable over three monthly exports

The master table in the last level needed a SUMIFS grid with carefully mixed references. A PivotTable builds the same grid from the same kind of stacked table with a few clicks, and it finds the row and column labels by itself, so no product can be left out of the list.

The stacked table

Mei’s sheet is three monthly marketplace exports pasted under each other: October in rows 2–8, November in rows 9–19, December in rows 20–28. Each export had the same columns; the month column (2025-10, 2025-11, 2025-12) is what keeps them apart, exactly like the source column in level 3.

The months are text labels written year first. That matters for the pivot: items are sorted A→Z, and 2025-10, 2025-11, 2025-12 sort in time order. Labels like Oct, Nov, Dec would sort alphabetically in Google Sheets (Dec, Nov, Oct). Excel rescues English month names with its built-in custom lists, but year-first labels work everywhere.

Building the pivot

Box	Field	Why
Rows	product	one line per pendant
Columns	month	one column per source export
Values	revenue, Sum	the money in each pair

The result fills G1:K8: the header rows, five pendants, and a Grand Total row and column. Ashford Linen Pendant Light earned 698.00 in October, 1,396.00 in November and 1,047.00 in December; a pendant with no sales in a month gets an empty cell, not a zero. That is the part Mei needs: the Dune Black Metal Pendant Light made 1,512.00 in October and nothing at all in December, while the Rowan Ceramic Chandelier sold nothing in October and 926.10 in December.

Where the Data Model fits in

The course version of this exercise keeps each month on its own sheet and lets Excel combine them. There are three honest ways to do that in Excel, and it helps to know what each really does:

• Append, then pivot. Turn each sheet into a Table (Ctrl+T), then Data → Get Data → Combine Queries → Append in Power Query, and Close & Load To… a PivotTable or the Data Model. This is what you just did by hand, and it refreshes when next month’s sheet is added.
• The Data Model with relationships. Insert → PivotTable, tick Add this data to the Data Model, then relate tables by a key (for example a sales table to a products table on product_id). Relationships join different tables side by side; they don’t stack same-shaped sheets. Use it to bring a category or a supplier from another table into the pivot.
• The old wizard. Alt, D, P opens the PivotTable and PivotChart Wizard with “Multiple consolidation ranges”. It works, but the result only knows Row, Column and Value, so you lose the other fields. Few people use it now.

This sheet has one worksheet, so the stacking is already done and the checker reads the pivot you build on it.

Common mistakes

• No source column. Stack the exports without a month column and the pivot can only show one combined total per product. Add the column before stacking, not after.
• Forgetting to refresh. An Excel PivotTable doesn’t update when the data changes: right-click → Refresh, or Data → Refresh All. Rows pasted under the old range are outside the pivot until you change its source, which is why a Table (Ctrl+T) is the better source: it grows by itself.
• Count instead of Sum. Dragging a number field into Values usually gives Sum, but a column with a blank or a text cell gets Count. Check the label: it should say “Sum of revenue”.