Pivot-Style Summary Reports with Formulas: UNIQUE, SUMIFS, Cross-Tabs & Subtotals
Learn what a PivotTable does and rebuild every part with live formulas: UNIQUE/SORT row labels, SUMIFS values over spilled labels, a rows × columns cross-tab with TRANSPOSE, % of row, column and grand total, counts, averages and distinct counts, report filters with (All), month and quarter grouping, a SUBTOTAL report and charts: 8 hands-on labs, graded instantly.
The first lesson is free — no account needed.
About this course
A pivot-style summary report course that rebuilds every part of a PivotTable with live formulas for one fictional outdoor retailer, Brightwater Outdoor Co., for Excel users, analysts and anyone whose reports must keep a fixed layout. You list sorted row labels and a two-level Region / Category list with UNIQUE and SORT, fill the values with one SUMIFS over the spilled labels and add a calculated field, build a regions × categories cross-tab with TRANSPOSE and one grid formula, show values as % of grand total, % of row and % of column with a 100% stacked chart, count orders, the average and largest order and distinct customers per region, add report filters with an (All) option and a drill-down list, group dates by month and quarter, and finish a SUBTOTAL report with a grand total that does not double count. The spill functions need Microsoft 365 or Excel 2021 and later. The first lab is free.
Lessons
- Row Labels: What a PivotTable Does, Rebuilt with UNIQUE and SORTFree
- Values: SUMIFS over the Spilled Labels (H2#) Pro
- Cross-Tabs: Rows × Columns with UNIQUE, TRANSPOSE and One SUMIFS Grid Pro
- Show Values As: % of Grand Total, % of Row and % of Column Pro
- Count, Average, Max and Distinct Count: COUNTIFS, AVERAGEIFS, MAXIFS Pro
- Report Filters: Criteria Cells, (All) and a Drill-Down List Pro
- Grouping Dates by Month and Quarter: EOMONTH and Text Keys Pro
- Capstone: A Subtotal Report, a Summary that Ignores It, and a Chart Pro