The Excel GROUPBY function (Excel 365 only) builds a PivotTable-style summary with a single formula: group rows by one or more fields, aggregate a values column with SUM, AVERAGE, COUNTA or any other function, and spill the result — totals, sorting and all. Unlike a PivotTable, it recalculates live: no Refresh button, ever.
SUM has no parentheses — you pass the function itself.
Syntax
| Argument | Description | |
|---|---|---|
row_fields | Required | The column(s) to group by — one column, or several adjacent columns for multi-level grouping. |
values | Required | The column(s) of values to aggregate. Must have the same number of rows as row_fields. |
function | Required | The aggregation, passed without parentheses: SUM, AVERAGE, COUNTA, MAX, MIN, MEDIAN, PERCENTOF, ARRAYTOTEXT... or your own LAMBDA. |
field_headers | Optional | 0 = no headers; 1 = headers in data, don’t show; 2 = no headers, generate them; 3 = headers in data, show them. |
total_depth | Optional | Which totals to show: 0 = none, 1 = grand total (default), 2 = grand + subtotals; negative values put totals at the top. |
bin_function | Optional | Bucketing applied to row_fields before grouping (e.g. group dates by year). |
sort_order | Optional | Column number to sort by; negative = descending. 2 sorts by the first value column, -2 descending. |
filter_array | Optional | TRUE/FALSE array that keeps only matching source rows, e.g. B2:B7="Widget". |
Excel 365 only. GROUPBY is a dynamic-array function rolled out to Microsoft 365; older versions show #NAME?. Leave empty cells below the formula for the result to spill into.
The core three: row_fields, values, function
Ninety percent of GROUPBY use is the first three arguments. Group this sales data by region:
| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Sales |
| 2 | North | Widget | $5,200 |
| 3 | South | Widget | $4,100 |
| 4 | North | Gadget | $3,900 |
| 5 | East | Widget | $6,300 |
| 6 | South | Gadget | $2,800 |
| 7 | East | Gadget | $4,700 |
One formula spills the whole summary — groups sorted ascending, grand total included:
| E | F | |
|---|---|---|
| 1 | East | $11,000 |
| 2 | North | $9,100 |
| 3 | South | $6,900 |
| 4 | Total | $27,000 |
row_fields can be multiple columns for nested grouping — Region then Product:
function is an eta-reduced lambda: you hand GROUPBY the function itself, no parentheses, no arguments. Anything aggregating an array works — SUM, AVERAGE, COUNTA, MAX, MIN, MEDIAN, STDEV.S, PERCENTOF (share of total), ARRAYTOTEXT (list the text values) — or a custom LAMBDA:
Try it: switch the aggregation function
Same data as above — pick a function and watch the spilled result change.
| E | F |
|---|
Totals, sorting, and filtering without helper columns
The optional arguments replace most PivotTable settings:
To aggregate several value columns at once, pass them together (e.g. C2:D7) — or wrap functions with HSTACK to apply a different aggregation per column.
GROUPBY vs PivotTable: GROUPBY recalculates instantly, spills into normal cells you can format and reference, and lives in one auditable formula. PivotTables still win for drag-and-drop exploration, slicers, and very large data. For columns-and-rows cross-tabs, use the sibling function PIVOTBY.
Errors & common pitfalls
#NAME? — your Excel doesn’t have GROUPBY. It requires a current Microsoft 365 build. There is no backport to Excel 2021 or earlier.
#VALUE! — mismatched ranges. row_fields and values must cover the same number of rows. Selecting A2:A7 against C2:C8 fails.
#SPILL! — something blocks the result. The summary needs empty cells below and beside the formula. Clear the blockage or move the formula.
Pitfall: writing SUM(). The function argument takes SUM, not SUM() and not "SUM". Parentheses or quotes produce errors.
Pitfall: including headers in the ranges. If row 1 holds headers and you select A1:A7, the header is treated as data — unless you set field_headers to 1 or 3 to tell GROUPBY about it.
Practice workbook
Frequently asked questions
Which Excel versions have GROUPBY?
Why do I write SUM without parentheses?
function argument is an eta-reduced lambda: GROUPBY wants the function itself, which it then calls once per group. SUM() would try to evaluate immediately, and "SUM" is just text — both fail.How do I sort the result by the totals instead of alphabetically?
sort_order (7th argument) with the column number; negative = descending. =GROUPBY(A2:A7, C2:C7, SUM, , 1, , -2) puts the biggest group first.How do I remove or expand the total rows?
Can I group by more than one column?
row_fields (e.g. A2:B100 for Region then Product). Non-adjacent columns can be combined with CHOOSECOLS or HSTACK. Add total_depth 2 for per-group subtotals.GROUPBY or PIVOTBY - which one do I need?
col_fields for a two-dimensional cross-tab — regions down, quarters across. Same family, same eta-lambda functions.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class