Purpose
Return value
Syntax
=PIVOTBY(row_fields,col_fields,values,function,[field_headers],[row_total_depth],[row_sort_order],[col_total_depth],[col_sort_order],[filter_array])- row_fields - The values for grouping rows.
- col_fields - The values for grouping columns.
- values - The values to aggregate.
- function - The calculation to run when aggregating.
- field_headers - [optional] 0 = No, 1 = Yes, don't show, 2 = No, generate, 3 = Yes, show.
- row_total_depth - [optional] 0 = None, 1 = Grand Totals, 2 = Grand and Subtotals, -1 = Grand Totals at top, -2 = Both at top.
- row_sort_order - [optional] Row to sort by as number. Negative numbers = descending order.
- col_total_depth - [optional] 0 = None, 1 = Grand Totals, 2 = Grand and Subtotals, -1 = Grand Totals at top, -2 = Both at top.
- col_sort_order - [optional] Column to sort by as number. Negative numbers = descending order.
- filter_array - [optional] Logic to remove specific rows.
How to use
The PIVOTBY function is designed to summarize data by grouping rows and columns. The result is a dynamic summary table created with a single formula. The result from the PIVOTBY function is similar to the output from a Pivot Table but without formatting. The table returned by the PIVOTBY function is fully dynamic and will immediately recalculate when source data changes.
Basic Example
The PIVOTBY function takes ten arguments but only the first four are required. In the worksheet shown below, we use the PIVOTBY function to summarize sales by city and year. The formula in cell F5 is:
=PIVOTBY(B5:B28,C5:C28,E5:E28,SUM)
For this example, PIVOTBY is configured with three arguments as follows:
- row_fields - provided as the range B5:B16, which contains city names.
- col_fields - provided as the range C5:C16, which contains years.
- values - provided as the range E5:E16, which contains sales amounts.
- function - provided as SUM, the calculation to perform during aggregation.
With the inputs above, the PIVOTBY function summarizes sales by city in rows and sales by year in columns. The entire table in G4:J8 is returned in one step. Notice that we have not included the header row in row_fields, col_fields, or values, because we are not asking PIVOTBY to generate a header automatically in this case. Note also that PIVOTBY includes a Total row and Total column by default.
Related functions
GROUPBY Function
The Excel GROUPBY function is designed to summarize data by grouping rows and aggregating values. The result is a summary table created with a single formula.
