Lookup & Reference
Excel 365
◉ 17 views
Dynamic Array
=PIVOTBY()
PIVOTBY function
Applies To:
Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for Mac
=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])
Hover argument names in the table for details.
Output typeArray
Popularity50 / 100
VolatileNo
Dynamic ArrayYes
Download sample .xlsx ▦ Try in Playground
Dedicated file for this function — safe & lightweight · sample data + live formulas
⬇ Download Sample Workbook
A dedicated .xlsx for =PIVOTBY() with Persian sample data & live formulas — open in Excel and practice.
- Sheet “Sample” — data table + formula in place
- Sheet “Guide” — syntax, arguments & tips from this page
- Instant & secure (token + rate limit)
Quiz this function
%d questions — syntax, example & errors — test yourself!
◧ Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
row_fields |
Range | Required | 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 |
Range | Required | A column-oriented array or range that contains the values which are used to group columns and generate column headers.The array or range may contain multiple columns. If so, the output will have multiple column group levels. |
values |
Range | Required | 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 |
Any type | Required | A lambda function or eta-reduced lambda (SUM, AVERAGE, COUNT, etc) that defines how to aggregate the values.A vector of lambdas can 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 |
Number | Required | 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 showNote: Automatic assumes the data contains headers based on the values argument. If the 1st value is text and the 2nd 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. |
row_total_depth |
Number | Required | Determines whether the row headers should contain totals. The possible values are:Missing: Automatic: Grand totals and, where possible, subtotals. 0: No Totals 1: Grand Totals 2: Grand and Subtotals -1: Grand Totals at Top -2: Grand and Subtotals at TopNote: For subtotals, row_fields must have at least 2 columns. Numbers greater than 2 are supported provided row_field has sufficient columns. |
row_sort_order |
Number | Required | A number indicating how columns should be sorted. Numbers correspond with 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 can be provided when sorting based on only row_fields. |
col_total_depth |
Number | Required | Determines whether the column headers should contain totals. The possible values are:Missing: Automatic: Grand totals and, where possible, subtotals. 0: No Totals 1: Grand Totals 2: Grand and Subtotals -1: Grand Totals at Top -2: Grand and Subtotals at TopNote: For subtotals, col_fields must have at least 2 columns. Numbers greater than 2 are supported provided col_field has sufficient columns. |
col_sort_order |
Number | Required | A number indicating how rows should be sorted. Numbers correspond with columns in col_fields followed by the columns in values. If the number is negative, the rows are sorted in descending/reverse order.A vector of numbers can be provided when sorting based on only col_fields. |
filter_array |
Logical | Required | A column-oriented 1D array of Booleans that indicate whether the corresponding row of data should be considered.Note: The length of the array must match the length of those provided to row_fields and col_fields. |
relative_to |
Text | Required | When using an aggregation function that requires two arguments, relative_to controls which values are provided to the 2nd argument of the aggregation function. This is typically used when PERCENTOF is supplied to function.The possible values are:0: Column Totals (Default) 1: Row Totals 2: Grand Totals 3: Parent Col Total 4: Parent Row TotalNote: This argument only has an impact if function requires two arguments. If you supply a custom lambda function to function, it should follow this pattern: LAMBDA(subset,totalset,SUM(subset)/SUM(totalset)) |
▣ Examples
Example 1
=PIVOTBY(B2:B100, A2:A100, D2:D100, SUM)
Example 2
=PIVOTBY(B2:B100, A2:A100, D2:D100, SUM, , 0, , 0)
💬 Comments & Q&A 0 approved
🔒 Your comment will be published after review.Share your thoughts — visible after admin approval
No comments yet — be the first!