# A to Z of Excel Functions: The PIVOTBY Function

18 December 2023

*Welcome
back to our regular A to Z of Excel Functions blog. Today we look at the PIVOTBY function. *

** **

**The
PIVOTBY function**

The **PIVOTBY** function allows you to
create a summary of your data via a formula, akin to a formulaic PivotTable. It supports grouping along two axes and
aggregating the associated values. For
instance, if you had a table of sales data, you might generate a summary of
sales by state and year.

It should be noted that **PIVOTBY** is a
function that returns an array of values that can spill to the grid. Furthermore, at this stage, not all features
of a PivotTable appear to be replicable by this function.

The syntax of the **PIVOTBY** function
is:

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

It has the following arguments:

**row_fields:**this is required, and represents a column-oriented array or range that contains the values which are used to group rows and generate row headers. The array or range may contain multiple columns. If so, the output will have multiple row group levels

**col_fields:**also

**values:**this is also required, and denotes a column-oriented array or range of the data to aggregate. The array or range may contain multiple columns. If so, the output will have multiple aggregations

**function:**also required, this is an explicit or eta reduced lambda (*e.g.***SUM**,**PERCENTOF**,**AVERAGE**,**COUNT**) that is used to aggregate**values**. A vector of lambdas may be provided. If so, the output will have multiple aggregations. The orientation of the vector will determine whether they are laid out row- or column-wise

**field_headers:**this and the remaining arguments are all optional. This represents a number that specifies whether the**row_fields, col_fields**and**values**have headers and whether field headers should be returned in the results. The possible values are:**Missing:**Automatic**0:**No**1:**Yes and don't show**2:**No but generate**3:**Yes and show

**values**argument. If the first value is text and the second value is a number, then the data is assumed to have headers. Fields headers are shown if there are multiple row or column group levels

**rows_total_depth:**this optional argument determines whether the row headers should contain totals. The possible values are:**Missing**: Automatic, with grand totals and, where possible, subtotals**0:**No TotalsGrand Totals**1:**Grand and Subtotals**2:**Grand Totals at Top**-1:**Grand and Subtotals at Top**-2:**

**row_fields**must have at least two [2] columns. Numbers greater than two [2] are supported provided**row_fields**has sufficient columns

**rows_sort_order:**again optional, this argument denotes a number indicating how rows should be sorted. Numbers correspond with the columns in**row_fields**followed by the columns in**values**. If the number is negative, the rows are sorted in descending / reverse order. A vector of numbers may be provided when sorting based upon only**row_fields**

**col_total_depth:**this optional argument determines whether the column headers should contain totals. The possible values are:**Missing**: Automatic, with grand totals and, where possible, subtotals**0:**No TotalsGrand Totals**1:**Grand and Subtotals**2:**Grand Totals at Top**-1:**Grand and Subtotals at Top**-2:**

**col_fields**must have at least two [2] columns. Numbers greater than two [2] are supported provided**col_fields**has sufficient columns

**col_sort_order:**again optional, this argument denotes a number indicating how rows should be sorted. Numbers correspond with the columns in**col_fields**followed by the columns in**values**. If the number is negative, these are sorted in descending / reverse order. A vector of numbers may be provided when sorting based upon only**col_fields**

**filter_array:**the final optional argument, this represents a column-oriented one-dimensional array of Boolean values [1, 0] that indicate whether the corresponding row of data should be considered. It should be noted that the length of the array must match the length of**row_fields**and**col_fields**.

Similar in many ways to** GROUPBY**,** PIVOTBY **is
fairly straightforward to use. Consider
the following Excel Table called **tbl**:

Consider the following formula:

**=PIVOTBY(tbl[Category],tbl[Year],tbl[Sales],AVERAGE)**

You can get more imaginative and sort in
descending order by the** AVERAGE **of **Rating**, *viz.*

**=PIVOTBY(tbl[Item],tbl[Year],tbl[Rating],AVERAGE,,,-2)**

*We’ll
continue our A to Z of Excel Functions soon.
Keep checking back – there’s a new blog post every other business day.*