GROUPBY and PIVOTBY: Summaries as Formulas

The problem

Summarizing data by group is the most common request in reporting: net sales by product, by market, by year. The classic answer is a PivotTable, and it remains the right answer for exploration. But a PivotTable is an object with its own life. It must be refreshed, it sits where you put it, other formulas can read it only through GETPIVOTDATA, and its layout is changed with the mouse. When a summary is one block of a larger model, or a table that must change the moment the data change, a formula is a better fit.

Until recently the formula was a combination of UNIQUE for the labels and SUMIFS for the values, as in the article on FILTER and UNIQUE. GROUPBY and PIVOTBY do the same job in a single function. GROUPBY returns a list of groups with an aggregated value for each, in rows. PIVOTBY returns a two-way table, with one field down the side and another across the top. Both are dynamic array functions, so they spill, and both are available in Microsoft 365. Microsoft's pages list a slightly different set of versions for the two, so check the page before sharing a file with users of Excel 2021 or 2024.

The examples use the companion file F17_GroupBy_PivotBy.xlsx. The Data sheet holds 30 illustrative rows in A1:F31: year, market, product, channel, units and net sales, both in thousands, for 2025 and 2026, three markets and three products.

GROUPBY

The full syntax is long, but only the first three arguments are required:

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

Row_fields is the column, or columns, that define the groups. Values is the column to aggregate. Function says how: SUM, AVERAGE, COUNT and so on, written by name, without parentheses. Microsoft calls this form an eta-reduced lambda: the name of a function is passed as an argument and GROUPBY applies it to each group. A full LAMBDA can be passed in the same place when the aggregation is not a built-in function.

The first block on the GROUPBY sheet gives the net sales of each product:

=GROUPBY(Data!$C$1:$C$31,Data!$F$1:$F$31,SUM,3)

The ranges include the header row, and the fourth argument, field_headers, tells GROUPBY so. The result has a header row, one row for each of the three products, and a total row, 6,671 in all. The group totals are 1,663 for Velorin, 1,189 for Cardexa and 3,819 for Oncavir.

Headers, totals and order

Three optional arguments shape the layout. They take numeric codes:

Subtotals need at least two row fields. In automatic mode, GROUPBY assumes the inputs have headers if the first value is text and the second is a number. When the values do not follow this pattern, set the argument explicitly. It is safer to always do so.

The second block ranks the products by net sales, with the grand total only:

=GROUPBY(Data!$C$1:$C$31,Data!$F$1:$F$31,SUM,3,1,-2)

Total_depth 1 keeps the grand total. Sort_order -2 refers to the second column of the output, the values, in descending order. The result lists Oncavir with 3,819, Velorin with 1,663 and Cardexa with 1,189. A ranked summary is one formula, and no sort command has to be repeated when the data change.

Several fields, several values

More than one column in row_fields creates levels. The third block groups by market and product:

=GROUPBY(Data!$B$1:$C$31,Data!$F$1:$F$31,SUM,3,2)

The output shows each market with its products and, with total_depth 2, a subtotal for each market: 2,101 for DE, 1,693 for UK and 2,877 for US. An optional last argument, field_relationship, decides whether the second column is sorted within the first, the default hierarchy, or independently, the table form, in which subtotals are not supported.

A values range of more than one column produces one aggregated column for each. The fourth block takes units and net sales together:

=GROUPBY(Data!$C$1:$C$31,Data!$E$1:$F$31,SUM,3)

For Velorin the result is 3,926 units and 1,663 of net sales, for Cardexa 6,506 and 1,189, for Oncavir 297 and 3,819. Seeing both columns side by side tells a story that one column hides: Cardexa sells many units at a low net price, and Oncavir few units at a high one.

Shares of the total

PERCENTOF is a function with two arguments, the group and the whole, and returns the share of one in the other. As the function of a GROUPBY it gives each group as a percentage of the total:

=GROUPBY(Data!$C$1:$C$31,Data!$F$1:$F$31,PERCENTOF,3)

The result is 57.2% for Oncavir, 24.9% for Velorin and 17.8% for Cardexa, with 100% in the total row. The cells need a percentage format, which is applied to the output area as to any other range.

Filtering inside the formula

The filter_array argument is a column of TRUE and FALSE values, one for each source row, that decides which rows enter the calculation. The sixth block uses the market typed in B1 (UK). Here the ranges start in row 2, with no headers, and field_headers is 0:

=GROUPBY(Data!$C$2:$C$31,Data!$F$2:$F$31,SUM,0,1,-2,Data!$B$2:$B$31=$B$1)

The UK has Oncavir with 998, Velorin with 398 and Cardexa with 297. Change B1 to DE and the block follows. The test is built like the include argument of FILTER, with the same operators for AND and OR, and it must have the same length as row_fields.

PIVOTBY

PIVOTBY adds a second grouping field, for the columns:

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

The arguments that exist in GROUPBY work in the same way, once for the rows and once for the columns. The first block of the PIVOTBY sheet puts the products down the side and the years across the top:

=PIVOTBY(Data!$C$1:$C$31,Data!$A$1:$A$31,Data!$F$1:$F$31,SUM,3)

The result is a table with a total column and a total row. Velorin falls from 880 to 783, Cardexa from 602 to 587 and Oncavir rises from 1,619 to 2,200. The column totals are 3,101 for 2025 and 3,570 for 2026. The second block puts the markets down the side and the products across the top, with the same layout of totals.

Percentages in a cross-tab

PERCENTOF needs a part and a whole. Block 3 repeats the first table and writes the whole into the function itself, with a LAMBDA that divides each cell by the total of the net sales column:

=PIVOTBY(Data!$C$1:$C$31,Data!$A$1:$A$31,Data!$F$1:$F$31,LAMBDA(x,PERCENTOF(x,Data!$F$2:$F$31)),3)

Every cell is now a share of the grand total of 6,671, and the whole table, not each column, adds up to 100%. This is where a model and an intuition can part. The table of absolute values says that Velorin and Cardexa are roughly stable. The table of shares shows Oncavir moving from 24.3% of all net sales in 2025 to 33.0% in 2026, while Velorin slips from 13.2% to 11.7%.

When PERCENTOF is passed by name, without a LAMBDA, PIVOTBY supplies the whole itself, and the relative_to argument says which one: 0, the default, for the column total, 1 for row totals, 2 for the grand total, 3 for the parent column total and 4 for the parent row total. With the default, each year column would add up to 100%.

Block 4 shows a table without any totals, by passing 0 for the row and the column total depth, which is convenient when the output feeds another formula or a chart:

=PIVOTBY(Data!$B$1:$B$31,Data!$A$1:$A$31,Data!$F$1:$F$31,SUM,3,0,,0)

The empty argument between the zeros leaves the row sort order at its default.

GROUPBY, PIVOTBY and the PivotTable

Microsoft's own page is clear on this point: PIVOTBY can produce output similar to a PivotTable, but it is not directly related to the PivotTable feature. The two tools solve overlapping problems with different strengths.

The functions win when the summary must be part of a model. They recalculate with the sheet, so no one has to press Refresh All. Their result can be read by other formulas through the spill operator, wrapped in TAKE to keep the top rows, or placed in a chart. Every choice, from the sort order to the filter, is written in the formula, so it can be audited and copied. The inputs can come from cells, as with the market in B1.

The PivotTable wins when someone needs to explore. It has slicers and timelines, drag-and-drop fields, double-click drill-down to the underlying rows and built-in grouping of dates and numbers. The functions have none of that, and their output carries no formatting of its own: number formats, borders and fills are applied to the area as for any range. The output of a function also needs free space, with the usual rules for #SPILL!: not inside an Excel table, not on top of other data.

A reasonable division of labor is to explore with a PivotTable and, once the question is settled, to build the permanent report with GROUPBY and PIVOTBY.

Opening the file

GROUPBY and PIVOTBY are new, and their formulas depend on the version of Excel that opens the file. If any block of the companion file shows #NAME? on opening, select the cell, press F2 and then Enter. Excel re-enters the formula and the block spills. If it still does, the version in use does not have the function.

Common mistakes

• Giving a function with parentheses, as in SUM(), where only its name is expected.
• Leaving field_headers on automatic when the first value of a column is text and the second is a number, or the reverse, and getting a header where there is none.
• Passing a filter_array whose length differs from the row fields, or one that includes the header row by mistake.
• Asking for subtotals with a single row field. They need at least two.
• Reading sort_order as a sheet column. The numbers refer to the output: the row fields first, then the values.
• Looking for slicers, drill-down or a refresh button. There are none, and none is needed.
• Putting the formula inside an Excel table, or above data that blocks the spill range.
• Expecting percentage results to format themselves. Apply the number format to the output area.

For further reading

• GROUPBY function - Microsoft Support
• PIVOTBY function - Microsoft Support
• Dynamic array formulas and spilled array behavior - Microsoft Support