Excel GROUPBY & PIVOTBY: The New Array Functions That Replace PivotTables

Published: June 17, 2026 | Category: Excel | Topic: Dynamic Arrays & New Functions

If you have spent years wrestling with PivotTables every time you need grouped summaries from your data, 2026 has brought you a better answer. Microsoft has officially rolled out two powerful new functions — GROUPBY and PIVOTBY — to Microsoft 365, and they are changing the way Excel users think about data aggregation. These functions combine the analytical power of PivotTables with the flexibility of formulas, letting you summarize data dynamically right inside a cell without a single click on the Insert tab.

In this tutorial, you will learn exactly what GROUPBY and PIVOTBY do, how they differ, and how to use them to build powerful, refreshable summaries in seconds.

What Are GROUPBY and PIVOTBY?

GROUPBY and PIVOTBY are dynamic array functions — they return results that spill automatically into multiple cells. They were designed to replicate PivotTable-style aggregation using formula syntax, which means they update instantly when your source data changes and can be nested inside other formulas.

GROUPBY groups data by one or more row fields and calculates an aggregate (like SUM, AVERAGE, or COUNT) for each group. Think of it as a one-dimensional summary — like a PivotTable with only row labels.

PIVOTBY takes things further by adding column grouping too. It is the full two-dimensional equivalent of a PivotTable — rows on one axis, columns on the other, and values in the middle.

The GROUPBY Function: Syntax and Step-by-Step

The syntax for GROUPBY is:

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

Say you have a sales table with columns: Region (A), Salesperson (B), Product (C), and Revenue (D). To get total revenue by Region:

=GROUPBY(A2:A100, D2:D100, SUM)

That single formula returns a spilled array showing each unique region and its total revenue. No formatting required, no Refresh button — it updates automatically the moment new data is added.

Step-by-Step: Build a Revenue Summary by Region

Open your data workbook and ensure your sales data is in a named Table (press Ctrl+T to create one). Name it SalesData.

Click on an empty cell — for example, F2 — where you want the summary to appear.

Type: =GROUPBY(SalesData[Region], SalesData[Revenue], SUM, 3, 1)

Press Enter. Excel will spill the results downward automatically.

The third argument (3) shows field headers; the fourth argument (1) adds a grand total row at the bottom.

The PIVOTBY Function: Two-Dimensional Summaries

PIVOTBY extends GROUPBY by adding a column grouping dimension:

=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [col_total_depth], [sort_order], [filter_array])

To build a cross-tabulation of Revenue by Region (rows) and Product (columns):

=PIVOTBY(SalesData[Region], SalesData[Product], SalesData[Revenue], SUM)

The result is a full pivot grid — every unique Region is a row, every unique Product is a column header, and the intersecting cells contain the summed revenue. This is the equivalent of building a PivotTable in about three seconds.

Key Parameters You Need to Know

function: The aggregate — SUM, AVERAGE, COUNT, MIN, MAX, MEDIAN, or any scalar-returning function including LAMBDA.

field_headers: 0 = no headers, 1 = include source headers, 2 = generate headers automatically, 3 = include generated headers (recommended default).

total_depth: 0 = no totals, 1 = grand total only, 2 = subtotals plus grand total.

filter_array: A TRUE/FALSE array to include only specific rows before aggregating. Great for date-range or category filters.

Combining with FILTER and SORT

One of the biggest advantages over PivotTables is that GROUPBY and PIVOTBY work seamlessly inside formula chains. To get total revenue by region but only for Q2 (where column E holds the quarter label):

=GROUPBY(A2:A100, D2:D100, SUM, 3, 1, , E2:E100="Q2")

GROUPBY vs PivotTables: When to Use Which

Use GROUPBY/PIVOTBY when you need formula-driven summaries that update instantly, want to embed results inside other formulas, or are building dashboards where manual PivotTable refreshes are error-prone.

Use PivotTables when you need advanced formatting, slicers for interactive filtering, custom calculated fields, or are sharing with users who prefer a traditional interface.

Availability

GROUPBY and PIVOTBY are available in Microsoft 365 (Current Channel) as of 2026. They require an active Microsoft 365 subscription. Excel 2021 standalone and earlier versions do not support these functions — colleagues on older versions will see #NAME? errors.

Conclusion

GROUPBY and PIVOTBY represent a genuine leap forward in how Excel handles data aggregation. By bringing PivotTable power into the formula bar, Microsoft has made it far easier to build dynamic, connected summaries that update without user intervention. Whether you are a data analyst building automated reports or a business user wanting cleaner summaries without fiddling with PivotTable layouts, these two functions deserve a prominent place in your toolkit.

Try replacing your next PivotTable with a GROUPBY formula and see how much faster your workflow becomes. Drop your experiments in the comments below, and subscribe to officelearner.net for more Excel tips published weekly.