Excel GroupBy as an alternative to Pivot Tables

Often times we are presented with a table of data, commonly exported from another system.  Turning that data into an Excel Pivot Table is easy once you get the hang of it but for some people, Pivot Tables are a whole new concept that seems scary.

Excel has a new function that will be coming out in a future release that can replace Pivot Tables for simple aggregation.   My new favorite is the GROUPBY function.

Let's say we have a table of Sales Data and I want to quickly get a summary of sales by sales person.  I can use the GROUPBY function to create a simple aggregation of sum of sales that looks like this.

That was pretty easy.  But what if we want to also see counts, averages, and percentages and want it sorted by sales in descending order.  Is that possible?  You bet it is!!!  Using more of the parameters in the GROUPBY combined with our friend HSTACK we can create a somewhat complex summary table as easy as this.
Well, that's a pretty nifty trick, and the formulas change as data is updated.  For instance, Gimli notices that there was a typo in his sales figure.  He didn't sell 781 but actually sold 7,810.  And, we got a new salesperson.  By changing the number all the formulas update.  BINGO!
Next time you want to do a quick aggregation in Excel, give GROUPBY or its sibling PIVOTBY a try.

If you are wanting to up your Excel or Vista game, feel free to reach out to us.  We would love to help you bring Construction and Technology together in a better way.

For more information on this function, check out this Microsoft Tech Community article that explores GROUPBY and PIVOTBY further.

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