For years, summarising large datasets in Excel meant creating pivot tables — a powerful but often cumbersome process that requires dragging fields into rows, columns, and values areas, and produces a separate output table that can be difficult to integrate into existing worksheets. Microsoft has introduced the GROUPBY function as a modern, formula-based alternative that generates dynamic, sortable, grouped summaries directly inside your cells. Unlike pivot tables, GROUPBY results automatically update when your source data changes, spill into adjacent cells using Excel’s dynamic array engine, and can be nested inside other formulas for advanced analysis.
What Is the GROUPBY Function?
GROUPBY is a dynamic array function available in Microsoft 365 (Excel for the web, Windows, and Mac with a 365 subscription). It groups rows of data by one or more columns and applies an aggregation function (such as SUM, AVERAGE, COUNT, MAX, or MIN) to produce a summarised output — all within a single formula.
The basic syntax is:
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array])- row_fields: The column(s) to group by (e.g., a column of product categories or department names).
- values: The column(s) containing the data to aggregate (e.g., a column of sales figures).
- function: The aggregation function to apply, such as SUM, AVERAGE, COUNT, MAX, or MIN.
- field_headers (optional): Specifies whether your data includes headers. Use 1 if headers are present, 0 if not, or 3 to generate new headers automatically.
- total_depth (optional): Controls whether grand totals and subtotals are included. Use 0 for no totals, 1 for grand totals, 2 for grand totals and subtotals, or -1 to place totals at the top.
- sort_order (optional): Determines the sort order of the grouped results. Use 0 for no sorting (preserves original order), 1 for ascending, or -1 for descending.
- filter_array (optional): A Boolean array (TRUE/FALSE) the same height as your source data, used to include or exclude specific rows before grouping.
A Practical Example: Summarising Sales by Region
Suppose you have a sales dataset in columns A through C:
| Region | Product | Revenue |
|---|---|---|
| North | Widget A | 5200 |
| South | Widget B | 3400 |
| North | Widget C | 7100 |
| East | Widget A | 4800 |
| South | Widget A | 6200 |
| East | Widget B | 3100 |
To summarise total revenue by region, enter this formula in any empty cell:
=GROUPBY(A2:A7, C2:C7, SUM, 0, 0, 1)This formula groups the data by the Region column (A2:A7), sums the Revenue column (C2:C7), excludes headers from the data range (0), excludes grand totals (0), and sorts the results in ascending order (1). The result spills automatically:
| Region | Total Revenue |
|---|---|
| East | 7900 |
| North | 12300 |
| South | 9600 |
Including Grand Totals
To add a grand total row at the bottom, change the total_depth argument to 1:
=GROUPBY(A2:A7, C2:C7, SUM, 0, 1, 1)The output will now include a final row showing the total across all regions:
| Region | Total Revenue |
|---|---|
| East | 7900 |
| North | 12300 |
| South | 9600 |
| Grand Total | 29800 |
This eliminates the need to create a separate SUM formula beneath your pivot table.
Grouping by Multiple Columns
GROUPBY supports grouping by more than one column simultaneously. To group by both Region and Product, pass both columns as the row_fields argument using the HSTACK function or by selecting a multi-column range:
=GROUPBY(A2:B7, C2:C7, SUM, 0, 0, 1)This produces a cross-tabulated summary showing total revenue for each unique Region-Product combination:
| Region | Product | Total Revenue |
|---|---|---|
| East | Widget A | 4800 |
| East | Widget B | 3100 |
| North | Widget A | 5200 |
| North | Widget C | 7100 |
| South | Widget A | 6200 |
| South | Widget B | 3400 |
Using Different Aggregation Functions
The third argument accepts any standard Excel aggregation function. Here are several useful alternatives:
| Function | What It Does | Example Use |
|---|---|---|
| SUM | Adds all values | Total revenue per region |
| AVERAGE | Calculates the mean | Average order value per product |
| COUNT | Counts entries | Number of transactions per salesperson |
| MAX | Finds the highest value | Largest single sale per region |
| MIN | Finds the lowest value | Smallest order per customer |
| MEDIAN | Finds the middle value | Median salary per department |
For example, to find the average revenue per region:
=GROUPBY(A2:A7, C2:C7, AVERAGE, 0, 0, 1)Filtering Data Before Grouping
The optional filter_array argument is extremely powerful. It accepts a TRUE/FALSE array that determines which rows are included in the grouping. This allows you to filter your data without modifying the source range.
For example, to group only rows where revenue exceeds 4000:
=GROUPBY(A2:A7, C2:C7, SUM, 0, 0, 1, C2:C7>4000)This is equivalent to creating a pivot table with a value filter — but it is far easier to write and modify. You can also reference a cell containing a threshold value to make the filter dynamic:
=GROUPBY(A2:A7, C2:C7, SUM, 0, 0, 1, C2:C7>E1)Changing the value in E1 instantly recalculates the grouped summary.
When to Use GROUPBY Instead of a Pivot Table
GROUPBY does not completely replace pivot tables. Each tool has distinct strengths:
| Feature | GROUPBY | Pivot Table |
|---|---|---|
| Inline results | ✔ Results appear directly in the worksheet | ✘ Creates a separate pivot table object |
| Automatic refresh | ✔ Updates instantly when data changes | ✘ Requires manual refresh or a VBA macro |
| Nestable in other formulas | ✔ Can be combined with FILTER, SORT, etc. | ✘ Cannot be referenced by dynamic array formulas |
| Interactive slicers | ✘ No built-in slicers | ✔ Full slicer and timeline support |
| Drag-and-drop interface | ✘ Formula-only | ✔ Visual field list interface |
| Calculated fields | ✔ Use custom LAMBDA functions | ✔ Built-in calculated field support |
Use GROUPBY when you need a lightweight, formula-based summary that stays in sync with your data, can be placed anywhere in your workbook, and can be combined with other dynamic array functions. Use pivot tables when you need interactive exploration with slicers, timelines, and the drag-and-drop field configuration interface.
Combining GROUPBY with Other Dynamic Array Functions
Because GROUPBY outputs a dynamic array, you can nest it inside other functions for advanced workflows:
- SORT: Sort the GROUPBY output by the aggregated column instead of the group column:
=SORT(GROUPBY(A2:A7, C2:C7, SUM), 2, -1)— this sorts by total revenue in descending order. - FILTER: Filter the GROUPBY results to show only groups above a threshold:
=FILTER(GROUPBY(A2:A7, C2:C7, SUM), GROUPBY(A2:A7, C2:C7, SUM)>5000) - VSTACK: Stack multiple GROUPBY results vertically to create a combined report from different datasets.
These combinations give you the analytical power of pivot tables with the flexibility and portability of standard Excel formulas. For users who are already comfortable with dynamic arrays, GROUPBY is a natural addition to the modern Excel formula toolkit.