Does Groupby replace pivot tables?

Does GROUPBY replace pivot tables?

The new functions GROUPBY and PIVOTBY are now available in the current channel of Microsoft Excel, and I’ve been playing with them to try to answer this question.

My conclusion is that it isn’t a simple yes/no answer. What is more useful to think about is “when” or “how” GROUPBY should be used instead of a pivot table – and vice versa.

Learning from the experts

I spent a fair amount of time watching videos and reviewing materials from experts. GROUPBY and PIVOTBY have been around in the Beta channel for a while, and the early videos focussed very much on the fact that you didn’t have to refresh these formulas for new data.

That didn’t seem to be that much of an advantage to me, compared to the other advantages that pivot tables give. In particular, the ability to add slicers and to drill down into data.

However, more recent material has really opened my eyes to the possibilities that these functions have. For example, this great video from Leila Gharani on more complex data groupings.

Mark Proctor (and others) have posted videos on how to recreate Slicer functionality with these formulas. Alan Murray has come up with at least two ways to replicate the drill down (“Show Detail”) feature of pivot tables. And David Benaim has written a comprehensive article with accompanying videos for the ICAEW comparing GROUPBY and traditional pivot table functionality.

The advantages of Groupby (and Pivotby)

The dynamic update of Groupby is by far the least exciting feature of it for me. It doesn’t take much to click refresh.

Of much more interest is the fact you have a lot more aggregation options. Some of these are built in, but they become infinite when you consider you can also add a Lambda. So for example, you can do a “distinct count” of your data which just isn’t possible in a pivot table.

It was also interesting to see that you can work with less than ideal data – for example, if someone has inserted subtotals in your raw data.

So does Groupby replace pivot tables?

No, even with all the additional functionality. Fundamentally, it depends on whether you’re trying to analyse data or producing a report.

In the illustration at the top, I’ve shown how a GROUPBY formula can be used to replicate the functionality that’s in this pivot table:

I probably prefer the look and feel of the GROUPBY result to the pivot table result.

But here’s the thing.

In order to have constructed my GROUPBY, I already had to have decided what I wanted my data to look like. The formula I ended up with was:

=DROP(GROUPBY(Debtors[[Who:]:[Type]],Debtors[owed],HSTACK(SUM,COUNT),1,1,-3,(Debtors[Date]>=’Customer analysis’!C5)*(Debtors[Date]<=’Customer analysis’!D5)),1)

Even putting aside any considerations about the complexity of this formula, where it becomes really tricky is if I decided that I wanted to see the data a different way. Perhaps I want my “Type” field to be a column now instead of a row.

With pivot tables, this is dead easy – you just drag the row field to the column field. With formulas, I’d need to change it to PIVOTBY for a start, and then potentially change a whole bunch of other bits.

So my conclusion is: If you’re using pivot tables because you want to explore your data or do some quick analysis, pivot tables are still the best thing to use. If you’re using pivot tables to construct regular reports or graphs, you’ll want to explore GROUPBY a bit more.

Find out more about my Excel training options.