Microsoft recently announced two new functions that provide similar functionality to pivot tables, but will update automatically for any change in the referenced data.

These functions are currently rolling out to users enrolled in the beta channel for Windows Excel and Mac Excel.

I’m excited to announce Excel’s new GROUPBY and PIVOTBY functions. These functions allow you to perform data aggregations using a single formula. And while these functions are extremely powerful, they are also simple to get started with. In fact, you can do a data aggregation with just 3 arguments, the same number as a simple XLOOKUP. 

Note: These are preview functions, their signature and results may change substantially before final release based on user feedback. Until final, we do not recommend using them in important workbooks.

https://techcommunity.microsoft.com/t5/excel-blog/new-aggregation-functions-groupby-and-pivotby/ba-p/3965765

See the link above for more details and also the article below from myOnlineTraininghub. Both include full descriptions and examples of the new functions:

The GROUPBY and PIVOTBY functions are a significant breakthrough in Excel’s toolbox of functions.

They let you easily group or aggregate data, a concept that’s been around in Excel since the days of PivotTables in 1993 and Power Query since 2010.

But what’s exciting about GROUPBY and PIVOTBY is that they simplify this process down to a formula.

This means any changes to the source data are instantly updated in your reports, unlike PivotTables which require a click of the Refresh button to update.

But what about Slicers, which are one of the best features of PivotTables?

Don’t worry, I’ll share a clever trick so you can still use Slicers with GROUPBY and PIVOTBY

https://www.myonlinetraininghub.com/excel-groupby-and-pivotby-functions