Excel like a finance pro.
GROUPBY & PIVOTBY
Dynamic arrays (Microsoft 365)A pivot table as a formula — so it refreshes without anyone remembering to refresh it.
Difficulty
1What is it?
The most common way a monthly pack goes wrong is a pivot table nobody refreshed: the numbers are last month's and everything looks fine. GROUPBY and PIVOTBY do the same rollup as a formula, so they recalculate like any other cell and cannot go stale. GROUPBY summarises down rows; PIVOTBY adds columns as a second dimension. Both take a function as an argument — SUM, AVERAGE, COUNT, or a LAMBDA of your own — which is the part that makes them worth learning beyond convenience.
2What it looks like
GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array])
· PIVOTBY(row_fields, col_fields, values, function, …)- row_fields
- The column to group down the side — department, customer, account.
- col_fields (PIVOTBY)
- The column to spread across the top, usually a month or a period.
- values
- The numbers to aggregate.
- function
- SUM, AVERAGE, COUNT, MAX, PERCENTOF — passed as the function itself, not in quotes. A LAMBDA works here too.
- [total_depth]
- 0 no totals · 1 grand total at the bottom (default) · 2 subtotals as well · negative puts totals at the top.
3When you use it
- Roll a transaction export up by department and month, live.
- Produce a summary that a stale refresh can never misreport.
- Aggregate with something a pivot can't do, by passing a LAMBDA.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Six transactions in, a department-by-month summary out. Edit any amount and the summary follows — no refresh.
=PIVOTBY(A2:A5, B2:B5, C2:C5, SUM) Jul Aug Total| A | B | C | D | |
|---|---|---|---|---|
| 1 | Dept | Month | Amount | Result |
| 2 | Sales | Jul | Jul Aug Total | |
| 3 | Sales | Aug | Sales $41,000 $38,500 $79,500 | |
| 4 | Marketing | Jul | Marketing $12,400 $15,100 $27,500 | |
| 5 | Marketing | Aug | A pivot would still show the old numbers until someone refreshed it. |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#NAME?The version of Excel doesn't have them — they're newer than the rest of the dynamic arrays.Fix: GROUPBY and PIVOTBY need a current Microsoft 365 build. A 2021 licence has FILTER and SORT but not these.
#SPILL!Something is sitting where the result needs to go, and the result changes size as the data does.Fix: Give it an empty region well clear of anything else — this is one formula whose footprint you don't control.
#VALUE!The row, column and value ranges are different heights.Fix: All of them must be the same number of rows — they're columns of the same table.
The function argument errorsIt was passed in quotes, as "SUM".Fix: Pass the function itself: SUM, not "SUM". That's unusual in Excel and catches everyone once.
6Better functions & alternatives
- A pivot table — More interactive and works in every version — at the cost of needing a refresh, which is exactly how a pack goes out with last month's numbers.
- SUMIFS — Fine for a handful of cells. GROUPBY is for when you don't know the list of departments in advance.
Want GROUPBY & PIVOTBY already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.