How to Use GROUPBY in Excel to Summarize Data

To summarize data with GROUPBY in Excel, click an empty cell and enter =GROUPBY(labels,values,function), for example =GROUPBY(A1:A7,B1:B7,SUM,3,1). Excel lists each label once, applies the function (here, SUM) to its values, and spills the summary into the cells below and to the right, with a grand total at the bottom. The summary recalculates whenever the source cells change.

Applies to: Excel for Microsoft 365, which is the version Microsoft’s GROUPBY function reference lists. The worked example below was checked in Excel for Windows, and every result in this article was recalculated independently. If Excel doesn’t recognize the function, jump to which Excel versions have GROUPBY.

What GROUPBY does

GROUPBY takes a column of repeated labels (product names, regions, months) and a column of numbers, finds each distinct label, and calculates one result per label. It replaces the old two-formula approach of listing items with UNIQUE and totaling each one with SUMIFS, and it can add totals, sort, and filter in the same formula.

Three terms come up throughout:

• Row fields: the column (or columns) you group by. Each distinct entry becomes one row of the summary.
• Values: the numbers (or text) that get calculated for each group.
• Spill: a single formula filling a whole block of cells. Only the top-left cell holds the formula; the rest show a thin blue border when selected and can’t be edited one by one.

Build your first GROUPBY summary

This sample lists six accessory orders. Type it into a blank worksheet so you can check every total before adapting the formula to your own data.

Item	Amount
Cable	18
Stand	32
Cable	12
Hub	45
Stand	28
Hub	15

1. Enter the sample in A1:B7. Put the headers Item and Amount in row 1 and type the amounts as plain numbers (no currency symbols typed as text).
2. Click cell D1. Leave column C empty and make sure D1:E5 has nothing in it, because the result needs that room to spill.
3. Type =GROUPBY(A1:A7,B1:B7,SUM,3,1) and press Enter. As you type =GROUPBY(, Excel shows the argument list in a tooltip below the cell; that tooltip is a quick sign your version has the function. Type SUM without parentheses.

4. Check the result. D1:E1 shows the headers Item and Amount, followed by Cable 30, Hub 60, Stand 60, and a Total row of 150. Items are sorted A to Z by default.

Here is what each part of that formula did:

• A1:A7 supplied the labels to group.
• B1:B7 supplied the amounts. It must cover exactly the same rows as the labels.
• SUM chose the calculation.
• 3 told GROUPBY that row 1 holds headers and that they should appear in the result.
• 1 asked for a grand total.

Confirm it’s live: change B2 from 18 to 20. With automatic calculation on, Cable becomes 32 and the total becomes 152. Change it back to 18 when you’re done. If nothing changes, select Formulas > Calculation Options > Automatic.

To remove the summary, select D1 (the cell that holds the formula) and press Delete. The whole spilled block disappears; your source data is untouched.

GROUPBY syntax and arguments

=GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array],[field_relationship])

Only the first three arguments are required. To skip an optional argument but set a later one, leave its slot empty with a comma, as in =GROUPBY(A2:A7,B2:B7,SUM,,,-2).

Argument	What it controls	Options
row_fields	What to group by	One column, or several adjacent columns for nested groups
values	What to calculate	One column, or several columns for several results
function	The calculation	SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, PERCENTOF, ARRAYTOTEXT, a LAMBDA, or several stacked with HSTACK
field_headers	Header handling	Omitted: Excel guesses. 0: no headers. 1: headers present, hide them. 2: no headers, generate them. 3: headers present, show them
total_depth	Totals	0: none. 1: grand total. 2: grand total plus subtotals. -1 and -2: the same, placed at the top. Omitted: grand total and subtotals where possible
sort_order	Sorting	A column number counted across row_fields then values; a negative number sorts descending
filter_array	Which source rows count	A TRUE/FALSE test the same height as row_fields
field_relationship	How multiple row fields relate	0: hierarchy (default). 1: table, each column sorted independently, no subtotals

Set field_headers yourself while you’re learning. When it’s omitted, Excel decides from the first entries whether row 1 is a header, and a wrong guess can turn a header into a group or a data row into a header. Use 3 when your ranges start at the header row and 0 when they start at the first data row.

Average, count, or calculate something else

The third argument takes a function name on its own, with no parentheses (Microsoft calls this an eta-reduced lambda). Swap SUM for another function to change the calculation. These results use the sample above.

Goal	Formula	Result
Average order per item	=GROUPBY(A2:A7,B2:B7,AVERAGE,0,0)	Cable 15, Hub 30, Stand 30
Number of orders per item	=GROUPBY(A2:A7,B2:B7,COUNT,0,0)	2 each
Largest order per item	=GROUPBY(A2:A7,B2:B7,MAX,0,0)	Cable 18, Hub 45, Stand 32
Share of the grand total	=GROUPBY(A2:A7,B2:B7,PERCENTOF,0,1)	Cable 0.2, Hub 0.4, Stand 0.4, Total 1
List the values in each group	=GROUPBY(A2:A7,B2:B7,ARRAYTOTEXT,0,0)	Cable “18, 12”, Hub “45, 15”, Stand “32, 28”

• COUNT vs COUNTA: COUNT counts only numbers. If the values column holds text (names, order IDs), COUNT returns 0, so use COUNTA instead.
• PERCENTOF divides each group’s sum by the sum of all values, as described on Microsoft’s PERCENTOF function page, so the results are fractions. Select them and choose Home > Percent Style to show 20% and 40%.
• Totals with AVERAGE: if you turn the total on with AVERAGE, the total row should average the six source orders (25), not the three group averages. Check this against your own data before reporting it.

Show several calculations side by side

Wrap the functions in HSTACK to get one column per calculation:

=GROUPBY(A1:A7,B1:B7,HSTACK(SUM,COUNT),3,1)

Each item now shows a SUM column and a COUNT column (Cable 30 and 2), and the totals row shows 150 and 6. Excel may add a label row naming each function above the results, so leave an extra row of space. Use VSTACK instead of HSTACK if you want the calculations stacked as rows.

Write your own calculation

When no built-in function does what you need, pass a LAMBDA. This one shows the gap between the largest and smallest order for each item:

=GROUPBY(A2:A7,B2:B7,LAMBDA(x,MAX(x)-MIN(x)),0,0)

Cable shows 6 (18 minus 12), Hub 30, and Stand 4. The x stands for each group’s values in turn.

Sort and filter the summary

Sort by total

The sixth argument picks a column of the output to sort by. Columns are counted across the row fields first, then the values. In the sample, column 1 is Item and column 2 is Amount, so -2 sorts by amount, largest first:

=GROUPBY(A1:A7,B1:B7,SUM,3,1,-2)

Hub and Stand both total 60, so either can come first; Cable (30) comes last, and the total row stays at the bottom. Use -1 to sort the items Z to A. A list of sort columns such as {1,-2} works only when every number points at a row field, according to Microsoft’s reference.

Include only some rows

The seventh argument is a TRUE/FALSE test applied to each source row before grouping. To count only orders of 20 or more:

=GROUPBY(A2:A7,B2:B7,SUM,0,0,-2,B2:B7>=20)

This returns Stand 60 and Hub 45. Cable disappears because neither of its orders (18 and 12) qualifies. The filter tests individual orders, not group totals, so it can’t hide items whose total is below a threshold. To do that, wrap the result in FILTER, or sort and read the top of the list.

Keep the test the same height as the other ranges. Here all three cover rows 2 through 7. Starting one range at row 1 and another at row 2 is one of the most common causes of #VALUE! or misaligned results.

Group by two fields with subtotals

Select two adjacent columns as row_fields to nest one group inside another. On a new sheet, enter this sample in A1:C9:

Region	Item	Amount
East	Cable	18
West	Stand	32
East	Hub	45
West	Cable	12
East	Stand	28
West	Hub	15
East	Cable	10
West	Stand	20

1. Click E1 and make sure the area to the right and below is empty.
2. Enter =GROUPBY(A1:B9,C1:C9,SUM,3,2). The 2 asks for subtotals as well as a grand total.
3. Check the result. East shows Cable 28, Hub 45, and Stand 28 with a subtotal of 101. West shows Cable 12, Hub 15, and Stand 52 with a subtotal of 79. The grand total is 180.

Subtotals need at least two row-field columns. With one column, total depth 2 gives an error, so use 0 or 1 there. To group by columns that aren’t next to each other, combine them first, for example HSTACK(A2:A9,D2:D9) or CHOOSECOLS(A2:D9,4,1); CHOOSECOLS also lets you swap the nesting order.

Setting the last argument to 1 (table relationship) sorts each column independently instead of keeping items under their region, and Microsoft notes that subtotals aren’t supported in that mode.

Group by month or let the list grow

Summarize dates by month

Row fields can be a calculated array, not just a range. Suppose the six orders in the first sample were placed on July 3, July 19, August 2, August 14, August 30, and September 5, 2026, entered as real dates in C2:C7. In an empty cell such as G1, enter:

=GROUPBY(TEXT(C2:C7,"yyyy-mm"),B2:B7,SUM,0,1)

The result is 2026-07: 50, 2026-08: 85, 2026-09: 15, and a total of 150. The year-month format keeps months in calendar order; a format such as “mmm” would sort the month names alphabetically. Date format codes can differ in some language versions of Excel.

The same trick cleans up labels. If one entry is “Cable ” with a trailing space, GROUPBY treats it as a separate item; =GROUPBY(TRIM(A2:A7),B2:B7,SUM,0,1) merges it back into Cable.

Include new rows automatically

A fixed range like A1:A7 won’t pick up an order typed in row 8. For a list that keeps growing, convert it to an Excel Table (Insert > Table) and name it, for example Orders, on the Table Design tab. Then use structured references:

=GROUPBY(Orders[Item],Orders[Amount],SUM,0,1)

Put this formula outside the table: a spilling formula can’t sit inside one. Microsoft’s page on structured references with Excel tables explains how these references adjust as rows are added. Another option for plain ranges is trimming a whole-column reference with TRIMRANGE, which drops the empty rows at the end.

Fix GROUPBY errors

• #SPILL!: something is in the way of the result. Clear the cells in that area, unmerge any merged cells, or move the formula somewhere with more room. A space typed into a cell also blocks it. The error also appears if the formula is inside an Excel Table.

• #NAME?: check the spelling first. If it’s correct, your Excel version doesn’t have GROUPBY (see below). A workbook built in Microsoft 365 and opened in an older version may show the formula with an _xlfn. prefix.
• #VALUE!: the ranges are different heights (for example A1:A7 with B2:B7), the filter test is a different height from row_fields, or total depth is 2 or -2 with only one grouping column.
• #CALC!: Microsoft’s #CALC! error page explains that Excel can’t return an empty set. With GROUPBY, the likely cause is a filter test that excludes every row, so loosen the condition. The same page notes that a LAMBDA typed on its own in a cell, without values to work on, also returns #CALC!.
• A header shows up as a group, or the first order is missing: set field_headers explicitly (3 with header rows, 0 without).
• Totals are 0 or too low: some amounts are stored as text. Look for green triangles or left-aligned numbers and convert them, or use COUNTA if you meant to count text.
• The same item appears twice: the labels differ by spelling, capitalization style, or an extra space. Clean the source or wrap the labels in TRIM.

Which Excel versions have GROUPBY

Microsoft’s function reference lists Excel for Microsoft 365. The function first appeared for Microsoft 365 Insiders on Windows and Mac before rolling out more widely. One-time purchase versions such as Excel 2024 and Excel 2021 aren’t listed, so expect #NAME? there.

The quickest way to check your own copy is to type =GROUP in a cell. If GROUPBY appears in the autocomplete list, you have it. If it doesn’t on a Microsoft 365 subscription, install the latest Office updates and try again; a business that holds updates back on a slower channel may get new functions later. Excel for the web and Excel for Mac may also support it on current subscriptions, but test there before sharing a workbook that depends on it.

GROUPBY, PIVOTBY, or a PivotTable?

Tool	Best when
GROUPBY	You want a summary by rows that updates instantly and can feed other formulas
PIVOTBY	You also want groups across the top, such as items down the side and months across
PivotTable	You want to drag fields around, drill down, use slicers, or you’re on Excel 2021 or older
SUMIF or SUMIFS	You need one or two totals, not a whole table

GROUPBY recalculates on its own, while a PivotTable has to be refreshed after the data changes; the steps for refreshing a pivot table cover the shortcuts and automatic options. On the other hand, a PivotTable is easier to rearrange and works in older versions. If you go that route, start with building a PivotTable step by step. For a cross-tab formula, see how PIVOTBY summarizes by rows and columns, and for a single condition, a SUMIF formula is simpler and also works in Excel 2021 and Excel 2024.

Frequently asked questions

Can the source data be on another sheet?

Yes. Click the other sheet while entering the formula, or type references such as Orders!A2:A7. A table name like Orders[Item] works from any sheet without a sheet prefix.

Can I format the GROUPBY result?

Yes. Number formats and fills applied to the spill area should stay in place when the values recalculate. Because the number of rows can change, formatting a few extra rows, or using a conditional formatting rule that looks for “Total” in the first column, keeps new rows consistent.

How do I keep a fixed copy of the result?

Select the spilled block, press Ctrl + C, then right-click a new spot and choose Paste Special > Values. The copy no longer changes with the source data.

Does GROUPBY change my source data?

No. It only reads the ranges you give it. Unlike the Subtotal command on the Data tab, it doesn’t insert rows into the list or require you to sort it first.

Can someone without GROUPBY open my workbook?

They can open it, but the summary cells may show #NAME? instead of the results. Paste a values-only copy into the file if it’s going to people on Excel 2021 or older.

Next, try the two-field example on your own data, then turn your source range into a table so new rows flow into the summary. If you only need to know how often each label appears, counting occurrences in Excel may be all you need.