Resources

Two new functions were announced last week: GROUPBY and PIVOTBY.

These provide a powerful yet simple way to perform data aggregations using a single formula thanks to Excel's dynamic array calculation engine.

If you’ve ever used regular PivotTables, you’ll know how great and intuitive they are. However, they do have a few downsides, such as not being as versatile as formulas and their inability to instantly update, so I'm sure this alternative is bound to sound tempting.

Before you get too excited, both functions are currently only available to Microsoft 365 Insiders on the Beta Channel (at the time of writing), so you'll have to wait a while before they reach the production version. That’s one good reason to become an Insider!

GROUPBY aggregates a range of values by row group.

The full syntax is:

`=GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array])`

It contains three compulsory arguments:

`row_fields`

— the column you want to group by.``

`values`

— the values you want to aggregate.-
`function`

— the function you want to aggregate them with (e.g. SUM, AVERAGE, COUNT).

Let’s look at a basic example in action.

Here’s a range containing some sales data.

We want to find the sales for each category. Therefore, we’re interested in two columns: Product Category and Total Amount.

Inside GROUPBY, reference `B1:B21`

(Product Category) in the `row_fields`

argument. Then do the same for `E1:21`

(Total Amount) in `values`

. Both of these ranges include the header.

In `function`

, specify SUM to add up the values.

The formula reads:

`=GROUPBY(B1:B21,E1:E21,SUM)`

It returns the three unique values in the Product Category column and sums the Total Amount values belonging to each. The default Total row is also included.

At this point, you’ve seen the minimum required for GROUPBY to work. Let’s move on to the optional arguments.

`[field_headers]`

— a number that determines whether column headers should be shown and in what way.``

`[total_depth]`

— a number that determines whether the row headers should contain totals.-
`[sort_order]`

— a number that indicates how the rows are sorted. -
`[filter_array]`

— a condition that determines what`row_fields`

groups should or shouldn't be included.

Continuing with the same example, headers can be displayed by choosing 3 for `[field_headers]`

.

Notice there's a bug here, as only the first field header is showing.

However, if the reference is adjusted to include the adjacent column (D1:E21), they all appear.

As helpful as the** **Total row is, there'll be times when there's a good reason to hide it. Thankfully, specifying 0 in `total_depth`

does this, although I'll omit this for the rest of the example.

`=GROUPBY(B1:B21,E1:E21,SUM,3,0)`

At this stage, the totals are are in ascending order, when descending is preferable. In `[sort_order]`

, putting 2 sorts the data by the Total Amount column, which is already happening anyway. However, preceding it with a minus (-) changes the order to descending.

`=GROUPBY(B1:B21,E1:E21,SUM,3,,-2)`

If you want to limit what's returned, include a condition in `[filter_array]`

. In this case, we want to return every product category except "Electronics", so reference the Product Category column (`B1:B21`

) and use the not equal to operator (<>) followed by the string.

`=GROUPBY(B1:B21,E1:E21,SUM,3,,-2,B1:B21<>"Electronics")`

PIVOTBY aggregates a range of values by row and column group.

The full syntax is:

`=PIVOTBY(row_fields,col_fields,values,function,[field_headers],[row_total_depth],[row_sort_order],[col_total_depth],[col_sort_order],[filter_array])`

Notice the additional arguments for columns and the renamed row ones? This function is similar to GROUPBY, but being able to group by column as well gives you added flexibility.

Using the same data as last time, let's reference the Customer ID column (`A1:A21`

) in `row_fields`

, the Product Category column (`B1:B21`

) in `col_fields`

, Total Amount (`E1:E21`

) in `values`

, and specify SUM as the `function`

.

`=PIVOTBY(A1:A21,B1:B21,E1:E21,SUM)`

Now we have a table that shows how much customers have spent on each category.

We can easily recreate the GROUPBY and PIVOTBY examples using PivotTables.

Let me quickly remind you of how to create a PivotTable…

Select anywhere in the data range and on the **Insert** tab, click **PivotTable **and then **From Table/Range**.

Choose a location for the PivotTable and select **OK**.

Here's GROUPBY's setup.

And here's PIVOTBY's. We'll use this one for the example below.

Let's change the first Customer ID entry in the source data from **CUST002 **to **CUST001**.

You might just notice a flicker in your peripheral vision. That's because the value 150 in `H3`

has moved to the row above in the PIVOTBY formula result.

However, cast your eyes over to the PivotTable, and you'll see it's still stuck in the previous state.

To update it, right-click anywhere on the PivotTable and select **Refresh**.

Great! It's now up-to-date.

For all the plus points about PivotTables, this is one of their fundamental flaws. Unless you want to go down the VBA route, you are stuck with this manual method.

GROUPBY and PIVOTBY are beta functions, so you might notice a few bugs, such as column headings not appearing and missing AutoComplete boxes for arguments. No doubt these issues will be ironed out, though.

Nevertheless, it provides a promising glimpse into the future.

Will you still need to use PivotTables? Well, probably if the following apply:

- You're working with a huge dataset that outsizes the worksheet grid.
- You want to display multiple aggregations, such as the sum, average, and count (this is possible but difficult to do with GROUPBY or PIVOTBY)
- You have a dusty old Excel copy instead of a Microsoft 365 one.

If you've already played around with the functions, what are your initial impressions?

## Conversation

As you comment, let's keep the discussion friendly and respectful to add value to the conversation.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere. uis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.