The new Excel features everybody loves nonetheless cannot do what a pivot desk does

When there are new instruments out there, it’s very simple for outdated instruments to turn into outdated; that’s sometimes the pure development of expertise. So, if you give individuals a few powerful new Excel functions that can spill results across the worksheet, it’s not shocking that you simply begin to hear speak of acquainted instruments changing into out of date.

GROUPBY and PIVOTBY definitely make a compelling case. They seem like shiny, newer variations of PivotTables, and it’s arduous to not fall in love with them. However, after utilizing them for a while, you’ll discover that they aren’t fairly the PivotDesk killer they may appear to be at first look.

What GROUPBY and PIVOTBY do

They look quite a bit like PivotTables, however the similarities solely go up to now

GROUPBY and PIVOTBY can feel like PivotTables which were stripped all the way down to their mathematical necessities, but it surely’s not that easy. They aren’t precisely the identical.

The GROUPBY operate handles single-axis aggregation. Here’s its primary syntax:

=GROUPBY(row_fields, values, operate, ...) 

You inform Excel which row fields outline the teams (if you choose a number of columns, you’ll create hierarchical groupings), which values to summarize, and the best way to summarize them. For occasion, suppose you have got a gross sales dataset and wish to see Total Profit and Average Units Sold grouped hierarchically by Region (main group) and Country (secondary group). You’d use a method like this:

=GROUPBY(A2:B100, HSTACK(N2:N100, I2:I100), VSTACK(SUM, AVERAGE), 3, 1)

Here, A2:B100 defines the hierarchical teams (Region and Country), whereas HSTACK(N2:N100, I2:I100) specifies the worth fields to summarize (Total Profit and Units Sold). VSTACK(SUM, AVERAGE) tells Excel the best way to summarize these values (SUM for Profit and AVERAGE for Units Sold), 3 tells Excel to incorporate headers and subtotals, and 1 tells Excel to type the leads to ascending order.

While GROUPBY summarizes information vertically utilizing row fields, PIVOTBY provides col_fields to interrupt down values each vertically and horizontally. You can management sorting and totals right here too, filter the supply information earlier than aggregation, and even use the relative_to argument for calculations similar to percentages of row, column, or grand totals. So, suppose you wish to analyze Total Profit (SUM) and Average Units Sold (AVERAGE), grouped vertically by Region and Country (row fields) and damaged down horizontally by Sales Channel (column subject). Your method would seem like this:

=PIVOTBY(A2:B100, D2:D100, HSTACK(N2:N100, I2:I100), VSTACK(SUM, AVERAGE), 3, 1, 1, 1, 1)

After the row_fields argument (A2:B100), there’s now a col_fields argument (D2:D100), which teams the columns in line with Sales Channel throughout the highest. In this method, 3 consists of headers, the primary 1 exhibits grand totals and subtotals for rows, the second 1 types rows alphabetically, the third 1 exhibits grand totals for columns, and the final 1 types the columns in ascending order.

The actually necessary distinction, although, is the place all of this occurs. GROUPBY and PIVOTBY are worksheet features. You enter a method, Excel evaluates it, after which spills the ensuing array into neighboring cells. If you modify the supply information, the outcome will recalculate robotically.

That opens up some helpful potentialities. A spilled PIVOTBY outcome can turn into a part of a dynamic dashboard, feed an XLOOKUP or FILTER method, or present a altering vary for a chart. You may go a custom LAMBDA function into the aggregation argument, providing you with significantly extra management than a standard Sum of Sales abstract. Sorting and filtering are additionally constructed into the formulation, so that you don’t essentially want helper columns or intermediate ranges simply to get the info into form.

These potentialities are why these features really feel like an improve. However, the features solely produce outcomes just like a PivotDesk; they aren’t the PivotDesk characteristic itself. The features reside within the method layer, whereas PivotDesk is an object-based reporting instrument with its personal interface and transport systems.

Where PivotTables nonetheless win

The features are highly effective, however they cannot fairly replicate the PivotDesk expertise

The largest downside with calling GROUPBY and PIVOTBY PivotDesk replacements is {that a} abstract isn’t essentially an interactive report. In a PivotDesk, double-clicking any abstract cell creates a brand new sheet displaying the underlying transaction information. The features provide the calculated outcome, however there’s no equal built-in drill-down motion.

The similar goes for increasing and collapsing hierarchies. PivotTables allow you to transfer between a high-level view and the underlying teams with acquainted broaden/collapse controls. A PIVOTBY or GROUPBY result’s a dynamic array: highly effective, versatile, and robotically resized, however in the end a calculated grid slightly than an interactive define.

Then there’s the PivotDesk Fields pane. Another member of your group can drag a subject from Filters to Rows, transfer one other into Values, and instantly see a distinct report. With PIVOTBY, altering the report construction means altering the method’s arguments. That’s hardly troublesome for an skilled Excel consumer, but it surely’s a really completely different expertise for somebody who would slightly drag “Region” into columns than edit a method.

PivotTables even have a a lot bigger ecosystem behind them. They can work with Excel’s Data Model and Power Pivot, bringing a number of associated tables and really massive datasets into the evaluation. DAX measures can present extra subtle enterprise logic, time intelligence, and KPIs. PivotTables may connect with exterior and enterprise information sources, whereas Excel can use them with reside connections to supported Power BI semantic fashions.

There are smaller conveniences, too. GETPIVOTDATA provides PivotTables a structured approach to retrieve particular values. With a GROUPBY or PIVOTBY array, you typically must construct your personal lookup logic across the spilled outcome.

Ironically, method flexibility can create its personal issues, too. Suppose you need a working whole or a period-over-period variance. The features are wonderful at aggregating values by row and column teams, however calculations that depend upon relationships between rows aren’t their pure power. You could find yourself wrapping the end in LET, LAMBDA, MAP, SCAN, BYROW, VSTACK, TAKE, DROP, MAKEARRAY, SUMIFS, or different dynamic-array functions. That can produce an impressively compact and automatic resolution, however it may well additionally go away you with a method that no one else on the group needs to the touch six months later.

Real-time recalculation isn’t robotically a win, both. Complex dynamic-array formulation over massive ranges can recalculate each time the worksheet adjustments. A PivotDesk’s pivot cache, in contrast, separates the report from raw-data edits till you refresh it. That additional step could be helpful when workbook efficiency is a matter.

Finally, there’s compatibility. GROUPBY and PIVOTBY depend upon fashionable Excel variations, together with Microsoft 365 and Excel 2024 and later. If you open the workbook in an older model, that intelligent new method will greet you with one among Excel’s most annoying error messages: “#NAME?”.

Different instruments, completely different jobs

GROUPBY and PIVOTBY aren’t actually killing PivotTables. They’re taking pivot-style evaluation and shifting it into Excel’s more and more highly effective method layer. That’s a implausible match if you need summaries that may robotically feed dashboards, dynamic arrays, charts, lookups, and different formulation.

PivotTables occupy a distinct territory. They’re interactive analytical environments, full with drill-down, drag-and-drop exploration, Data Model integration, and connections to bigger business-intelligence ecosystems. So the selection isn’t essentially about selecting a winner. It’s about choosing the right instrument for the job.