PIVOTBY

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])

Returns the grouping of your data along two axis and aggregates the associated values.

row_fieldsA column-oriented array or range that contains the values which are used to group rows and generate row headers.
col_fieldsA column-oriented array or range that contains the values which are used to group columns and generate column headers.
valuesA column-oriented array or range of the data to aggregate.
functionThe function that defines how to aggregate the values:
SUM
PERCENTOF
AVERAGE
MEDIAN
COUNT
COUNTA
MAX
MIN
PRODUCT
ARRAYTEXT
CONCAT
STDEV.S
STDEV.P
VAR.S
VAR.P
MODE.SNGL
LAMBDA
field_headers(Optional) A number that specifies whether the row_fields, col_fields and values have headers and whether field headers should be returned in the results:
0 = No
1 = Yes but don't show
2 = No but generate
3 = Yes and show
row_total_depth(Optional) Determines whether the row headers should contain totals:
0 = No totals
1 = Grand totals
2 = Grand and subtotals
-1 = Grand totals at top
-2 = Grand and subtotals at top
row_sort_order(Optional) A number indicating how rows should be sorted.
col_total_depth(Optional) Determines whether the column headers should contain totals:
0 = No totals
1 = Grand totals
2 = Grand and subtotals
-1 = Grand totals at top
-2 = Grand and subtotals at top
col_sort_order(Optional) A number indicating how rows should be sorted.
filter_array(Optional) A column-oriented 1D array of Booleans that indicate whether the corresponding row of data should be considered.
relative_to(Optional) Controls which values are provided to the 2nd argument of the aggregation function:
0 = Column totals (default)
1 = Row totals
2 = Grand totals
3 = Parent column total
4 = Parent row total

REMARKS
* For an illustrated example refer to the page under Data Functions
* This function was added in Microsoft 365 (version 2409).
* This function can create a Dynamic Array Formula.
* You can use the GETPIVOTDATA function to return the data obtained from a pivot table.
* You can use the GROUPBY function to return the grouping of your data along one axis and aggregates the associated values.
* You can use the PERCENTOF function to return the percentage that a subset makes up from a given data set.
* link - insider.microsoft365.com/en-us/blog/new-aggregation-functions-in-excel-groupby-and-pivotby
* This function was first released in December 2023.
* For the Microsoft documentation refer to support.microsoft.com

 A
1??


© 2025 Better Solutions Limited. All Rights Reserved. © 2025 Better Solutions Limited Top