Excel 2024: Moving from GROUPBY to PIVOTBY

Once you are familiar with GROUPBY as described in the previous pages, it is easy to move on to the PIVOTBY function.

The PIVOTBY function adds three new arguments to the GROUPBY arguments to allow for column fields. The three arguments are Col_Fields, Col_Total_Depth, and Col_Sort_Order. They work just like Row_Fields, Total_Depth and Sort_Order in GROUPBY, but they apply to the Columns fields in the resultant array.

The following image shows a simple PIVOTBY with one row field and one column field.

Why do you need PIVOTBY when you could just as easily create a pivot table? Because the PIVOTBY formula will automatically recalculate. You won't have to rely on the person using the workbook to click Refresh. Are pivot tables dead? No - there are still plenty of pivot features that are not possible with PIVOTBY. For example, all of the Show Values As choices are not supported in PIVOTBY.