Pivot Tables
Not a function, but a tool. Summarizes a raw table by grouping rows into categories and aggregating a value, without writing a single formula.
A pivot table answers “how does this number break down by that category” without SUMIFS, COUNTIFS, or AVERAGEIFS anywhere in the sheet. It groups a raw list of rows by whatever field you put in Rows (and Columns, if you add one), then aggregates whatever field you put in Values across each group.
Text fields can only be Counted; numeric fields can be Summed, Averaged, Counted, or take the Min/Max. Leave Columns empty and you get a simple one-column breakdown. Add a Columns field and the output becomes a full grid: one row per Rows value, one column per Columns value, which is where pivot tables stop being a shortcut and start being the fastest way to answer a genuinely two-dimensional question.
Syntax
| Argument | Description |
|---|---|
| Rows | Required. The field to group by, one group per row of the pivot table, e.g. Region. |
| Columns | Optional. A second field to group by, laid out across the top instead of down the side. Turns a one-dimensional pivot into a cross-tab. |
| Values | Required. The field to aggregate inside each group, plus which aggregation to use. |
The mistake almost everyone makes early on
Reaching for Sum (or getting a confusing blank/zero result) on a field that's actually text, or reaching for Sum when the real question was a headcount. If you want “how many transactions,” that's Count, not Sum: the pivot-table equivalent of COUNTIF. A close second: forgetting that adding a Columns field turns a single list of totals into a full 2D grid, with one cell for every Rows × Columns combination rather than one combined number.
A real example: turning a transaction export into a desk P&L cross-tab
Say you've exported a raw, transaction-level trade blotter, one row per trade, with columns for Desk, Month, and P&L, and you need a summary of P&L by desk, by month, for a reporting pack:
The result is a grid with one row per desk and one column per month, each cell the summed P&L for that desk in that month: the same output a dozen nested SUMIFS formulas would produce, built in seconds and automatically correct even as new trade rows get added to the source data. This is exactly the kind of “summarize this raw export” task that comes up constantly in reporting-heavy finance roles, and it's a common live-Excel-test ask precisely because it's fast to verify whether someone actually knows the tool or is guessing.
Worked examples
| A | B | |
|---|---|---|
| 1 | Region | Sales |
| 2 | West | 320 |
| 3 | East | 210 |
| 4 | West | 300 |
| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Sales |
| 2 | West | Widget | 200 |
| 3 | West | Gadget | 150 |
| 4 | East | Widget | 90 |
Common mistakes
- Summing or averaging a text field. Only Count makes sense there, since there's no number to add up.
- Reaching for Sum when the real question is a headcount: that is Count, the pivot-table equivalent of COUNTIF.
- Forgetting a Columns field turns the output into a 2D grid, not a longer list: every Rows value gets one cell per Columns value, not one combined total.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice Pivot Tables →