What changed
A PivotTable is fast to build but has to be refreshed, breaks quietly when the source range grows and cannot easily feed another formula. GROUPBY and PIVOTBY give you the same kind of summary as a spilled formula. Change a number in the data and the summary updates straight away.
Syntax
- GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])
- PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to])
- total_depth: 0 no totals, 1 grand total, 2 grand total and subtotals; negative values put them at the top. Subtotals need at least two row fields.
- sort_order: a column number - row fields first, then values. A negative number sorts descending.
- filter_array: a TRUE/FALSE column the same height as the data. Only TRUE rows are counted.
Five finance examples
Sales by region (region in A, amount in D):
=GROUPBY(A2:A500, D2:D500, SUM)Region and product with subtotals per region:
=GROUPBY(A2:B500, D2:D500, SUM, , 2)Closed deals only, largest region first (column 2 is the value column):
=GROUPBY(A2:A500, D2:D500, SUM, , , -2, E2:E500="Closed")Each region's share of the total:
=GROUPBY(A2:A500, D2:D500, PERCENTOF)Regions down the side, months across the top (month in C):
=PIVOTBY(A2:A500, C2:C500, D2:D500, SUM)Will this break your workbook?
- Fixed ranges stop growing. A2:A500 misses row 501. Point the formula at an Excel Table column, or use a trim reference such as A2:.A5000.
- Headers are guessed. If field_headers is left blank, Excel decides whether the first row is a header. Include the header row and pass 3 so the result is labelled the same way every time.
- The result needs room to spill. Anything in the way turns the whole table into #SPILL!. Give it its own area or sheet.
- Other people's Excel. Colleagues without a current Microsoft 365 build see #NAME?. Paste values into copies that go outside the team.
- Charts and lookups that read the summary need to follow the spill - reference it as F2# rather than a fixed range.
PivotTable or GROUPBY?
Keep a PivotTable for exploring - dragging fields around to find the story. Use GROUPBY or PIVOTBY for the summary that goes into a monthly report, where it must update without anyone remembering to press Refresh.
