Pivot-Style Reports With Formulas

A pivot table cross-tabulates data: rows for one category, columns for another, and totals filling the grid. A classic example is Region down the side, Quarter across the top, and Sales in each cell.

Pivot tables are great but they need manual refreshing and they sit in a fixed block. A formula-driven pivot rebuilds itself live whenever the data changes.

In this lesson you will lay out row headers, column headers, and a body of SUMIFS formulas that compute every intersection automatically.

We will use a sheet named Sales with these columns: Region in A, Quarter in B, and Amount in C, across rows 2 to 500.

The report we want looks like this:

• Row labels: each unique Region down column E.
• Column labels: Q1, Q2, Q3, Q4 across row 1 from F to I.
• Body: total Amount for each Region and Quarter pair.

Every body cell answers one question: how much did this region sell in this quarter?