How to Replace Excel Pivot Tables with the New GROUPBY and PIVOTBY Functions

Pivot tables have long been the standard for data summarization, but their reliance on manual refreshes and isolation from formula logic often creates bottlenecks in fast-moving spreadsheets. Microsoft has addressed this fundamental flaw by introducing two new dynamic array functions in Excel: GROUPBY and PIVOTBY. These functions allow users to build live, auto-updating summaries directly within a cell, eliminating the friction of traditional pivot tables.

The primary issue with traditional pivot tables is that they do not refresh automatically when source data changes. If a user forgets to click refresh, the summary displays outdated information. Furthermore, pivot tables exist outside standard formula logic, making it difficult to feed their results into functions like FILTER or SORT. The new functions resolve this by generating live dynamic arrays that stay perfectly in sync with the source data.

The GROUPBY function acts as a lightweight pivot table that lives entirely within a formula, generating a one-dimensional summary with categories in rows and aggregated values next to them.

1. Select the column you want to group by as your first argument. This establishes the primary categories for your summary rows.
2. Define the column you want to aggregate as the second argument. This tells Excel which numerical or text values to calculate.
3. Choose your aggregation function, such as SUM, AVERAGE, or MAX. This determines how the data is processed without requiring helper columns.

=GROUPBY(C2:C500, E2:E500, SUM)

Unlike traditional methods, GROUPBY also supports text aggregation functions, allowing users to combine text values from multiple rows into a single summarized cell.

When you need a two-dimensional summary - such as viewing sales by product category and by month - the PIVOTBY function adds a second grouping axis to create a full cross-tabulation.

1. Assign the row field as your first argument. This creates the vertical axis of your matrix.
2. Assign the column field as your second argument. This generates the horizontal axis, such as months or regions.
3. Select the values range and the aggregation function. This populates the intersecting cells with the calculated totals.

=PIVOTBY(C2:C500, A2:A500, E2:E500, SUM)

Because the output is a live array, you can easily wrap it in other functions or use it as a dynamic data source for charts that update instantly.

While these functions are highly efficient, they do not entirely replace traditional objects for every use case. If your workflow relies heavily on slicers, timelines, or the ability to double-click a cell to drill down into the underlying rows, standard pivot tables remain necessary. Additionally, traditional tables handle automatic date grouping natively, whereas the new functions require wrapping dates in YEAR() or MONTH() manually.

It is also important to note that these formulas are exclusive to Microsoft 365, meaning they will not render for users on older versions of Excel.

The introduction of GROUPBY and PIVOTBY signals a clear shift in how Microsoft envisions data analysis, moving away from static objects toward composable, formula-driven reporting. For professionals managing weekly reports on recurring datasets, the elimination of the manual refresh step removes a critical point of human error.

By integrating summarization directly into the formula engine, Excel is empowering users to build more resilient, interconnected dashboards. As dynamic arrays continue to evolve, the traditional pivot table may soon be relegated to highly specific, interactive presentations rather than everyday data crunching.