How to Use the PIVOTBY and GROUPBY Dynamic Array Functions in Microsoft Excel

For decades, analyzing tabular datasets in Microsoft Excel required creating traditional PivotTables. While powerful, PivotTables require manual refreshes, cannot easily nest inside standard spreadsheet formulas, and frequently disrupt layout flow when data expands. The introduction of GROUPBY and PIVOTBY delivers dynamic array formulas that perform multi-dimensional aggregations, automatic total calculations, and custom sorting instantly—updating in real time whenever underlying data points change.

GROUPBY vs PIVOTBY Syntax Comparison

Both functions aggregate data using familiar summary math functions (such as SUM, AVERAGE, COUNT, or PERCENTOF), but differ in dimensions:

• GROUPBY: Aggregates data across one or more vertical row fields (similar to a one-dimensional summary table).
• PIVOTBY: Aggregates data across both vertical row fields and horizontal column fields (creating a complete two-dimensional cross-tabulation matrix).

=GROUPBY(row_fields, values, function, [headers], [total_depth], [sort_order], [filter_array])
=PIVOTBY(row_fields, col_fields, values, function, [headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array])

Parameter	Function Role	Common Values
row_fields	Categorical rows	One or more adjacent columns (e.g., A2:B100).
col_fields	Horizontal categories	The column to cross-tabulate horizontally (e.g., C2:C100).
values	Numeric metrics	The data to summarize (e.g., Sales[Amount]).
function	Aggregation logic	SUM, AVERAGE, PERCENTOF, COUNT, MAX, MEDIAN.
headers	Header generation	0 (No), 1 (Yes, don’t show), 2 (No, generate), 3 (Yes, show).
total_depth	Totals & Subtotals	0 (No totals), 1 (Grand totals), 2 (Grand totals & subtotals).

Example 1: Building a Dynamic Multi-Column Summary with GROUPBY

Suppose you have a transaction table in columns A:C containing Department (Col A), Region (Col B), and Revenue (Col C). To calculate total revenue by Department and Region with grand totals and automatic headers:

=GROUPBY(A1:B100, C1:C100, SUM, 3, 1)

How it behaves:

1. Excel groups by Department, then sub-groups by Region.
2. It applies the SUM calculation across each unique combination.
3. Because headers = 3, Word preserves your original column names.
4. Because total_depth = 1, it appends a dynamic Grand Total row at the bottom.

Example 2: Cross-Tabulating Sales Across Quarters with PIVOTBY

To recreate a full two-dimensional PivotTable showing Sales Reps as rows, Fiscal Quarters as horizontal columns, and Revenue as aggregated values:

=PIVOTBY(A2:A100, B2:B100, C2:C100, SUM, 0, 1, , 1)

This generates an expanding matrix where Sales Reps line the vertical axis, Quarters header the horizontal axis, and both row and column grand totals populate automatically.

Example 3: Filtering and Calculating Market Share with PERCENTOF

GROUPBY and PIVOTBY include an optional filter_array parameter, allowing dynamic exclusions without helper columns. Furthermore, pairing GROUPBY with the PERCENTOF lambda function calculates percentage distributions automatically:

=GROUPBY(A2:A100, C2:C100, PERCENTOF, 0, 1, -2, B2:B100="North America")

In this formula:

• PERCENTOF displays each department’s revenue as a percentage of the total.
• sort_order = -2 sorts descending by the aggregated percentage column.
• filter_array = B2:B100="North America" isolates only the North American territory.

By replacing static PivotTables with GROUPBY and PIVOTBY, financial analysts and data modelers maintain lightweight, reactive workbooks that never require manual refreshes or VBA scripts.