Pivot-Style Summary Reports with Formulas: UNIQUE, SUMIFS, Cross-Tabs & Subtotals

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Build the row labels of a summary report for Brightwater Outdoor Co.: a sorted category list, a distinct count and a two-level Region / Category list, all with one formula each.
• Fill the Values column of the category report with one SUMIFS, add a grand total, a calculated field (revenue per unit) and a version sorted largest first.
• Build Brightwater's revenue matrix, regions down and categories across, with a header formula, one grid formula and row and column totals.
• Turn the revenue matrix into three percentage views and chart each region's category mix as a 100% stacked column chart.
• Summarise Brightwater's orders per region four ways: number of orders, average order value, largest order and number of distinct customers.
• Add a Channel filter and a Region filter with an (All) option to the category report, then list the orders behind one number, like a PivotTable's double-click drill-down.
• Roll Brightwater's H1 2026 order lines up to months and quarters, like a PivotTable's date grouping, with a helper key column and SUMIFS over the spilled keys.
• Finish Brightwater's printed subtotal report with SUBTOTAL, add a grand total that does not double count, rebuild the region summary with SUMIFS and chart it.