How to Use the GROUPBY Function in Excel: Syntax, Examples, and PIVOTBY Comparison

The GROUPBY function in Microsoft Excel 365 lets you summarize a table with a single formula—no PivotTable to refresh and no helper columns to maintain. In this 2026 guide, you will learn the official syntax, work through realistic sales examples, add totals, sorting, and filters, and see how GROUPBY compares with PIVOTBY, classic PivotTables, Google Sheets, and LibreOffice Calc.

What Is the GROUPBY Function in Excel?

GROUPBY creates a summary of your data along one axis and aggregates the related values. If you have a sales table, you can return total revenue by region, by product, or by region and product together—all from one formula that spills automatically when the source data changes.

Microsoft made GROUPBY and its two-axis companion, PIVOTBY, generally available in Excel for Windows, Mac, and Excel for the web in the Current Channel in October 2024. Official documentation still lists it under Excel for Microsoft 365 only. It is not part of perpetual Excel 2021 or Excel 2024.

You get the most reliable results when the source is an Excel Table. Structured references such as Sales[Revenue] expand as you add rows, so the summary stays current without rewriting the formula.

GROUPBY Function Syntax and Arguments

According to Microsoft’s official GROUPBY function documentation, the syntax is:

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

Argument	Required?	What it does
row_fields	Yes	Column(s) used to group rows and build labels. Multiple columns create multiple group levels.
values	Yes	Numeric column(s) to aggregate. Multiple columns produce multiple aggregations.
function	Yes	Aggregation such as SUM, AVERAGE, COUNT, MAX, MIN, or PERCENTOF. A vector of functions is allowed.
field_headers	No	Default is Automatic. Use 0 (no), 1 (yes, hide), 2 (no, generate), or 3 (yes, show).
total_depth	No	Default is Automatic. Use 0 (none), 1 (grand totals), 2 (grand totals and subtotals), or -1/-2 to place those totals at the top. Subtotals need two or more columns in row_fields.
sort_order	No	A number pointing to a column in row_fields, then in values. Negative numbers sort descending.
filter_array	No	TRUE/FALSE values the same height as row_fields. Only TRUE rows are included.
field_relationship	No	0 Hierarchy (default) or 1 Table. Subtotals are not supported in Table mode.

Automatic header detection looks at the values argument. If the first cell is text and the second is a number, Excel treats the first row as headers. With an Excel Table, you can usually leave field_headers blank.

How to Use the GROUPBY Function Step by Step

The examples below use a table named Sales. Paste the sample into A1, press Ctrl+T (Windows) or Command+T (Mac), and name the table Sales.

Year	Region	Product	Units	Revenue
2025	West	Laptop	40	48000
2025	West	Mouse	120	2400
2025	East	Laptop	35	42000
2025	East	Keyboard	80	4000
2026	West	Laptop	52	62400
2026	West	Keyboard	90	4500
2026	East	Laptop	48	57600
2026	East	Mouse	150	3000
2026	North	Laptop	28	33600
2026	North	Mouse	70	1400

Enter the formula in an empty cell with space to spill into. You do not copy it down. If Excel returns #SPILL!, clear the blocking cells and try again.

Practical GROUPBY Examples

Example 1: Total sales by region

This is the shortest useful formula—three required arguments only:

=GROUPBY(Sales[Region], Sales[Revenue], SUM)

Excel groups every unique region, sums Revenue, and adds a grand total. West returns 117,300, East returns 106,600, and North returns 35,000, for a total of 258,900.

Example 2: Region and product with subtotals

Because Region and Product sit next to each other in the table, you can pass both as row_fields. Setting field_headers to 3 shows the column names, and total_depth to 2 adds subtotals plus a grand total:

=GROUPBY(Sales[[Region]:[Product]], Sales[Revenue], SUM, 3, 2)

Each region now has a subtotal, and each product appears nested under its region. This is the closest formula equivalent to a one-axis PivotTable with subtotals turned on.

Example 3: Sort, filter to 2026, and show percentages

This formula keeps 2026 rows only, hides the grand total, sorts regions by revenue from high to low, and returns both a sum and a percentage of the filtered total. In sort_order, 1 is Region and 2 is Revenue, so -2 sorts by Revenue descending:

=GROUPBY(Sales[Region], Sales[Revenue], HSTACK(SUM, PERCENTOF), , 0, -2, Sales[Year]=2026)

West leads 2026 at 66,900, East follows at 60,600, and North is 35,000. PERCENTOF shows each region as a share of that filtered set, and HSTACK places the two aggregations in side-by-side columns.

GROUPBY vs PIVOTBY vs PivotTables

GROUPBY summarizes along one axis (row labels only). PIVOTBY adds a second axis so distinct values become column headers. To see revenue by region across years, use:

=PIVOTBY(Sales[Region], Sales[Year], Sales[Revenue], SUM)

Regions appear in rows, years in columns, and summed revenue at each intersection. Microsoft notes that PIVOTBY is a worksheet function, not the PivotTable feature, even when the output looks similar.

Need	Best choice
A live formula that updates with the sheet	GROUPBY (one axis) or PIVOTBY (rows and columns)
Interactive drag-and-drop, slicers, or a large model	Create a PivotTable and refine it in the Field List
A result you can reference with other formulas	GROUPBY or PIVOTBY, because the spill range stays formula-driven
Very large data, Power Query, or multiple cache-based reports	A PivotTable, or load the data with Get & Transform first

Use GROUPBY when you want a summary that behaves like SUMIFS, only more compact and easier to extend. Keep PivotTables when colleagues need to slice, rearrange, or explore the same data without editing formulas.

GROUPBY on Windows, Mac, the Web—and Other Spreadsheets

On Windows and Mac, use a Microsoft 365 Current Channel build and confirm the function appears in the formula list. Excel for the web includes it for Microsoft 365 subscribers. Android and iOS apps can open workbooks that already contain the formula, but authoring may lag, so build and test on Windows, Mac, or the web.

Google Sheets does not have GROUPBY. The official equivalent is the QUERY function. With the same sample in A1:E11 (headers in row 1, Region in column B, Revenue in column E), this returns total revenue by region:

=QUERY(A1:E11, "SELECT B, SUM(E) GROUP BY B LABEL SUM(E) 'Revenue'", 1)

LibreOffice Calc also has no GROUPBY function. Use Insert > Pivot Table (DataPilot) to group and aggregate, or pair SUMIF/SUMPRODUCT with a unique list of labels. Those approaches work on Windows, macOS, and Linux, but they are not formula-identical to Excel’s version.

Common GROUPBY Mistakes

• #NAME? usually means the workbook is open in a perpetual version (Excel 2021 or Excel 2024) or in an older Microsoft 365 build that does not include the function.
• #SPILL! means another value is blocking the output. Clear that range or move the formula.
• #VALUE! often appears when row_fields, values, and filter_array are not the same height, or when function is not a valid aggregation.
• Missing subtotals: total_depth 2 (or -2) needs at least two columns in row_fields. It also requires the default Hierarchy relationship, not Table mode.
• Unexpected headers or an extra total row: set field_headers and total_depth explicitly instead of relying on Automatic.

GROUPBY Function FAQ

Is the GROUPBY function available in Excel 2024 or Excel 2021?

No. Microsoft’s documentation lists the GROUPBY function for Excel for Microsoft 365. Perpetual Excel 2021 and Excel 2024 do not receive this function. If you need a similar report in those versions, build a PivotTable.

Does GROUPBY work in Excel for Mac and Excel for the web?

Yes. Microsoft announced general availability for Excel for Windows, Mac, and the web in the Current Channel in October 2024. You still need a qualifying Microsoft 365 subscription.

When should I use a PivotTable instead of GROUPBY?

Choose a PivotTable when you want slicers, a Field List, or teammates to rearrange the report without editing a formula. Choose GROUPBY when you want a compact, formula-based summary that other cells can reference directly.

Start with the three required arguments—what to group, what to add up, and how to aggregate—then add headers, totals, sort order, or a filter when the report needs more detail.