Quick Navigation
PIVOTBY Function
Summary
The PIVOTBY function creates dynamic data summaries through formulas, enabling row and column grouping with flexible aggregation. Perfect for generating PivotTable-style reports directly in cells without using the PivotTable interface.
Syntax
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])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| row_fields | Array/Range |
Yes | Column-oriented array/range for row grouping and headers. Multiple columns create hierarchical levels. |
| col_fields | Array/Range |
Yes | Column-oriented array/range for column grouping and headers. Multiple columns create hierarchical levels. |
| values | Array/Range |
Yes | Column-oriented array/range containing data to aggregate. Multiple columns enable multiple metrics. |
| function | Lambda/Function |
Yes | Aggregation method (SUM, AVERAGE, COUNT, etc.) or custom LAMBDA. Vectors enable multiple aggregations. |
| field_headers | Number |
No | Controls header display: 0=No, 1=Yes hidden, 2=No generated, 3=Yes shown (Automatic if omitted). |
| row_total_depth | Number |
No | Row totals control: 0=None, 1=Grand, 2=Grand+Sub, -1=Grand top, -2=Grand+Sub top (Auto default). |
| row_sort_order | Number/Vector |
No | Sort rows by row_fields/values columns. Negative = descending. |
| col_total_depth | Number |
No | Column totals control: 0=None, 1=Grand, 2=Grand+Sub, -1=Grand top, -2=Grand+Sub top. |
| col_sort_order | Number/Vector |
No | Sort columns by col_fields/values columns. Negative = descending. |
| filter_array | Boolean Array |
No | 1D boolean array to filter data rows. Must match row_fields length. |
| relative_to | Number |
No | For 2-arg functions (like PERCENTOF): 0=Col totals, 1=Row, 2=Grand, 3=Parent Col, 4=Parent Row. |
Using the PIVOTBY Function
PIVOTBY transforms raw data into structured summaries using formulas. Specify row and column grouping fields, the values to aggregate, and the aggregation function. Optional parameters provide complete control over headers, totals placement, sorting order, data filtering, and percentage calculations.
Common PIVOTBY Examples
Basic Sales Summary by Product and Year
=PIVOTBY(C2:C76,A2:A76,D2:D76,SUM)Creates sales summary table with products as rows, years as columns, SUM aggregation. Headers detected automatically.
Sales Summary with Grand Totals at Bottom
=PIVOTBY(C2:C76,A2:A76,D2:D76,SUM,,,1)Adds grand totals for rows and columns at bottom/right positions.
Multi-level Grouping with Subtotals
=PIVOTBY(E2:E76,B2:D76,G2:G76,SUM,3,2,-2)Uses 3-column row fields for Region/Category/Product hierarchy with subtotals and descending sales sort.
Multiple Metrics Side-by-Side
=PIVOTBY(C2:C76,A2:A76,F2:H76,{SUM,AVERAGE,COUNT})Shows Sum, Average, and Count of sales in separate columns for each product/year combination.
Frequently Asked Questions
Common Errors and Solutions
#VALUE! error
Cause: Mismatched array lengths between row_fields, col_fields, values, or filter_array
Solution: Ensure all input arrays have identical row counts.
No output or blank cells
Cause: All data filtered out by filter_array or no matching groups
Solution: Check filter_array booleans and ensure grouping fields have data.
Unexpected sorting
Cause: row_sort_order/col_sort_order referencing non-existent columns
Solution: Column numbers start at 1 for first row_field/col_field column.
PERCENTOF not working
Cause: relative_to omitted or custom lambda not following LAMBDA(subset,totalset,...) pattern
Solution: Set relative_to appropriately and verify lambda syntax.
Notes
- PIVOTBY returns dynamic arrays - select multiple cells for output
- Unlike PivotTables, PIVOTBY formulas recalculate with data changes
- Supports LAMBDA for custom aggregations like weighted averages
- filter_array must be exactly same length as input data rows
- Automatic header detection: text in row 1 + number in row 2 = headers present
Compatibility
Available in: Excel 365, Excel 2021
Not available in: Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2021