If you have spent years building pivot tables in Excel — clicking through the interface, dragging fields, refreshing data, and reformatting after every update — there is genuinely exciting news. Microsoft has introduced two powerful new functions, GROUPBY and PIVOTBY, that let you aggregate and analyse data using a formula instead of a pivot table. In 2026 these functions are stable, widely available, and rapidly becoming the preferred tool of advanced Excel users.
This guide explains what GROUPBY and PIVOTBY do, how they differ from pivot tables, and how to use them with practical, real-world examples.
The Problem With Pivot Tables
Pivot tables are a powerful feature, but they come with frustrating limitations:
- They do not update automatically when source data changes unless you click Refresh
- They cannot be referenced easily inside other formulas
- They require a specific source range format and resist dynamic input
- They generate static output that is hard to combine with other calculations
- They are cumbersome to create programmatically or via automation
GROUPBY and PIVOTBY solve all of these limitations. They are dynamic array functions — the output automatically spills into adjacent cells and updates instantly when the source data changes.
Introducing GROUPBY
GROUPBY aggregates data by one or more grouping columns and returns a dynamic array of results. Think of it as a formula-based version of the Row Labels area in a pivot table.
The basic syntax is:
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array])
Here is what each argument means:
row_fields: The column(s) you want to group by — equivalent to Row Labels in a pivot table
values: The column(s) containing the numbers you want to aggregate
function: The aggregation function — SUM, AVERAGE, COUNT, MAX, MIN, etc.
field_headers: Optional — 0 to exclude headers, 1 to include them
total_depth: Optional — controls whether grand totals are shown
GROUPBY in Practice: A Sales Example
Suppose you have a sales table in A1:C500 with columns Region, Product, and Revenue. To see total revenue by region, you would write:
=GROUPBY(A2:A500, C2:C500, SUM)
Excel instantly returns a two-column dynamic array: Region names in the first column and their summed revenue in the second. When new rows are added to your source data, the GROUPBY result updates automatically — no refresh button required.
To group by both Region and Product simultaneously:
=GROUPBY(A2:B500, C2:C500, SUM)
This returns a three-column result: Region, Product, and Total Revenue for each combination.
Introducing PIVOTBY
PIVOTBY is the two-dimensional counterpart to GROUPBY. It groups data by both rows and columns simultaneously — exactly like a traditional pivot table, but as a formula.
The syntax is:
=PIVOTBY(row_fields, col_fields, values, function, [row_headers], [col_headers], [total_depth], [sort_order], [filter_array])
The key addition compared to GROUPBY is col_fields: the column whose distinct values become column headers in the output.
Using the same sales table, to see revenue broken down by Region (rows) and Product (columns):
=PIVOTBY(A2:A500, B2:B500, C2:C500, SUM)
Excel returns a matrix with regions as rows, products as columns, and the sum of revenue at each intersection — a dynamic pivot table that lives in a formula.
Filtering and Sorting Your Results
Both GROUPBY and PIVOTBY support an optional filter_array argument that lets you filter the source data before aggregation:
=GROUPBY(A2:A500, C2:C500, SUM, , , , C2:C500>1000)
This groups revenue by region but only includes rows where Revenue exceeds 1,000.
You can also combine these functions with FILTER, SORT, and SORTBY for sophisticated data pipelines that would previously have required multiple pivot tables or complex helper columns.
GROUPBY vs Pivot Tables: Which Should You Use?
Both tools have their place in 2026. Here is a practical guide to choosing:
- Use GROUPBY/PIVOTBY when you need results that update automatically without user interaction
- Use GROUPBY/PIVOTBY when the aggregated output needs to feed into other formulas
- Use GROUPBY/PIVOTBY in dashboards where the data structure needs to stay dynamic
- Use traditional pivot tables when you need drag-and-drop interactivity and quick ad-hoc exploration
- Use traditional pivot tables when non-technical users need to rearrange the analysis themselves
Advanced Tip: Combining With Other Dynamic Array Functions
Because GROUPBY and PIVOTBY return arrays, they compose beautifully with other dynamic functions:
SORT your results: =SORT(GROUPBY(A2:A500, C2:C500, SUM), 2, -1) — sort by revenue descending
Filter the output: =FILTER(GROUPBY(…), GROUPBY(…)>5000) — show only groups above a threshold
Take the top N: =TAKE(SORT(GROUPBY(…),2,-1), 10) — return the top 10 groups
This composability makes GROUPBY and PIVOTBY far more flexible than pivot tables for formula-driven reporting.
Availability and Requirements
GROUPBY and PIVOTBY are available to Microsoft 365 subscribers on the Current Channel (Windows and Mac). As of 2026, they are also available in Excel for the web. If you do not see these functions, check that your Microsoft 365 installation is up to date.
Conclusion
GROUPBY and PIVOTBY represent a significant leap forward in Excel’s analytical capabilities. By bringing aggregation into the formula engine, Microsoft has made it possible to build fully dynamic, auto-refreshing reports that respond to data changes in real time. If you have not yet explored these functions, set aside 30 minutes this week to try them on your own data — the productivity gains are immediate and substantial.
Start with a simple =GROUPBY formula on your next dataset. Once you experience the instant, automatic refresh, you may find yourself reaching for it instead of the pivot table interface more often than you expect.
