How to Use the GROUPBY and PIVOTBY Functions in Microsoft Excel

Summarising large datasets in Microsoft Excel has traditionally depended on PivotTables or complex array formulas combining SUMIFS, UNIQUE, and INDEX/MATCH. While PivotTables offer powerful interactive capabilities, they require manual or VBA-driven refreshes whenever underlying records change. Modern versions of Microsoft Excel introduce two high-performance formula-based aggregation functions: GROUPBY and PIVOTBY. These native dynamic array functions automatically group rows and columns, execute multi-field aggregations, and compute totals in real time without refreshing.

Understanding GROUPBY vs PIVOTBY

  • GROUPBY: Performs one-dimensional grouping. It aggregates data by one or more row fields, generating summary statistics alongside optional subtotals and grand totals.
  • PIVOTBY: Performs two-dimensional grouping. It groups records across both rows and columns simultaneously, effectively creating a fully functional formula-driven pivot grid.

Syntax and Parameter Breakdown

GROUPBY Syntax

=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array])
  • row_fields: The range containing the categories you want to group by (e.g., Department, Region).
  • values: The numeric or quantitative range you want to summarise (e.g., Sales, Quantity).
  • function: The aggregation operation (e.g., SUM, AVERAGE, COUNT, MAX, MIN, MEDIAN, PERCENTOF).
  • field_headers: Controls header display: 0 (No), 1 (Yes, don’t show), 2 (No, generate), 3 (Yes and show).
  • total_depth: Determines total generation: 0 (No totals), 1 (Grand totals), 2 (Grand and subtotals), -1 (Totals at top).

PIVOTBY Syntax

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

PIVOTBY adds dedicated parameters for column field categories (col_fields) and separate total controls for both row and column margins.

Practical Example 1: Creating a Grouped Summary with GROUPBY

Imagine a sales transactions table in range A1:C100 where column A is Region, column B is Sales Rep, and column C is Revenue.

To aggregate total revenue by Region and Sales Rep with grand totals and subtotals, enter:

=GROUPBY(A2:B100, C2:C100, SUM, 0, 2)

The dynamic formula automatically:

  1. Extracts unique combinations of Region and Sales Rep.
  2. Calculates the sum of Revenue for each group.
  3. Inserts intermediate subtotal rows for each Region.
  4. Appends a final Grand Total row at the base of the dynamic array spill range.

Practical Example 2: Generating a Two-Dimensional Grid with PIVOTBY

Using the same dataset, suppose you wish to evaluate monthly sales performance across geographical regions. Column A contains Region, column D contains Month, and column C contains Revenue.

To construct a cross-tabulated matrix with Months as column headers and Regions as row labels:

=PIVOTBY(A2:A100, D2:D100, C2:C100, SUM, 0, 1, , 1)

In this formula:

  • A2:A100 defines the vertical row categories (Regions).
  • D2:D100 defines the horizontal column categories (Months).
  • C2:C100 supplies the numerical values.
  • SUM aggregates the revenue intersecting each Region and Month.
  • The 1 arguments enable both Row Grand Totals and Column Grand Totals.

Filtering Data Directly Within the Aggregation

Unlike standard PivotTables where slicers or report filters must be configured, both GROUPBY and PIVOTBY accept an inline boolean filter array. For example, to aggregate sales only for the “West” region:

=GROUPBY(B2:B100, C2:C100, AVERAGE, 0, 1, 1, A2:A100="West")

This filters records before calculation, preserving dynamic workbook performance without auxiliary calculation columns.

Key Advantages Over Traditional PivotTables

  • Instantaneous Recalculation: Formulas update immediately when transaction figures change, eliminating the need to click “Refresh Data”.
  • Dynamic Spill Behaviour: New categories or products added to the source table automatically expand the output array.
  • Formulas within Formulas: The spill output can be directly referenced by nested functions, such as SORT, FILTER, or CHOOSEROWS, for advanced reporting dashboards.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.