9. Dynamic Arrays

9.11 PIVOTBY, and PIVOTBY vs PivotTable

By Ravindra Bagale · Last updated: 6 October 2026

Example first. Ravina wants a cross-table: cities down the side, platforms across the top, sales in the middle, and totals on both edges. That is exactly what a PivotTable does, but Ravina wants it as a formula so it updates by itself and can feed other formulas. PIVOTBY is GROUPBY with a second grouping across the columns.

First look at the data (before → after):

Expected result — K2 spills (empty crossing cells stay blank)

K2: =PIVOTBY(C2:C11,D2:D11,G2:G11,SUM)
row_fields = City   col_fields = Platform   values = Amount   function = SUM

The syntax in plain English

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 first four arguments are the same idea as GROUPBY plus col_fields, the field that goes across the top. The optional ones work in pairs: one for rows and one for columns (total depth and sort order). relative_to is only used with PERCENTOF: 0 = share of the column total (default), 1 = share of the row total, 2 = share of the grand total, 3 = parent column total, 4 = parent row total.

Example 2: count orders by Category and Status.

=PIVOTBY(E2:E11,I2:I11,G2:G11,COUNT)

Example 3: only Delivered orders. filter_array is the 10th argument, so there are six commas after SUM.

=PIVOTBY(C2:C11,D2:D11,G2:G11,SUM,,,,,,I2:I11="Delivered")

Example 4: platform mix inside each city (each row adds up to 100%). Use PERCENTOF with relative_to = 1 (row totals). Format the result as Percentage.

=PIVOTBY(C2:C11,D2:D11,G2:G11,PERCENTOF,,,,,,,1)

Read it like this: 81.3% of all sales came from Blinkit; Pune is the only city with both platforms (20.2% Amazon Now, 79.8% Blinkit).

Example 5: biggest city first, platforms in reverse order. Sort numbers count the row_fields columns first and then the values, so row_sort_order -2 means "sort by the values (each city's total), largest first". col_sort_order -1 sorts the platform names Z to A.

=PIVOTBY(C2:C11,D2:D11,G2:G11,SUM,,,-2,,-1)

The rows come out as Solapur, Pune, Nashik, Nagpur, Kolhapur, Sambhaji Nagar, and the columns as Blinkit, Amazon Now, Total.

PIVOTBY vs PivotTable: which one when?

Microsoft's own page notes that PIVOTBY is not directly related to the PivotTable feature: it is a separate formula that happens to make a similar report.

How these results were checked: this function is not available in LibreOffice, so every expected value on this page was computed in Python and cross-checked in LibreOffice with classic formulas that give the same numbers. The layout of the spill follows Microsoft's documentation. Microsoft reference: PIVOTBY function.

Practice task (try it before you open the answer):

1. Make a PIVOTBY with Category down the side, Platform across the top and SUM of Qty in the middle.
2. Which category sold the most units on Blinkit?
3. Change the formula so the result shows each platform's share of the column total (PERCENTOF, default relative_to).