Summarising multi-column business records in Microsoft Excel has traditionally required inserting a Pivot Table. While Pivot Tables offer powerful drag-and-drop flexibility, they introduce significant operational friction: they require manual refreshing whenever underlying data changes, interfere with surrounding formulas, and cannot easily spill dynamic array results across adjacent worksheets. Microsoft resolved these limitations by introducing the PIVOTBY function in Microsoft 365.
PIVOTBY is a modern dynamic array formula that generates fully formatted two-dimensional summary tables—calculating row headers, column headers, aggregated values, and subtotals—entirely through a formula cell. When new sales or inventory rows are appended to your table, PIVOTBY recalculates automatically in real time. In this guide, we explore the syntax of PIVOTBY, walk through practical examples, configure custom sorting and subtotals, and compare its capabilities against standard Pivot Tables.
Understanding the Syntax and Arguments of PIVOTBY
The PIVOTBY function accepts several arguments, allowing you to control row grouping, column matrix splitting, and mathematical aggregations:
| Argument | Required? | Purpose | Example Input |
|---|---|---|---|
row_fields |
Yes | One or more columns to group as vertical row headers | A2:A100 (e.g. Sales Rep or Region) |
col_fields |
Yes | One or more columns to pivot horizontally across columns | B2:B100 (e.g. Product Category or Quarter) |
values |
Yes | The numeric data range to aggregate | C2:C100 (e.g. Revenue or Units Sold) |
function |
Yes | The aggregation calculation (SUM, AVERAGE, COUNT, MAX, etc.) | SUM or ETA LAMBDA |
headers |
No | Controls whether source data includes headers (0=No, 1=Auto, 2=Yes) | 3 (Show row and column header labels) |
total_depth |
No | Determines grand total and subtotal placement (0=None, 1=Grand totals) | 1 (Grand totals at bottom and right) |
sort_order |
No | Defines numeric sort sequence (positive for asc, negative for desc) | -1 (Sort descending by row total) |
If you only require a single vertical dimension without horizontal column pivoting, you can instead use the GROUPBY function in Excel to generate clean one-dimensional summary tables.
Step-by-Step Tutorial: Building a Cross-Tabulation with PIVOTBY
Suppose you maintain an active sales register where Column A lists the Region (North, South, East, West), Column B contains the Product Line (Laptops, Monitors, Keyboards), and Column C contains the Invoice Total. Follow these steps to build a real-time matrix summary:
- Click into an empty cell on your report sheet where you want the top-left corner of the summary table to appear (for example, cell
E2). Ensure there are sufficient empty rows and columns below and to the right so the array does not encounter a#SPILL!error. - Type the following formula:
=PIVOTBY(A2:A100, B2:B100, C2:C100, SUM) - Press Enter. Excel immediately generates a dynamic table: unique Regions form the left-hand row headers, Product Lines form the column headers across the top, and total sales figures populate the matrix intersections.
- Excel automatically appends a horizontal Grand Total row at the bottom and a vertical Grand Total column on the far right.
- Update any value in Column C. Observe how the PIVOTBY output recalculates instantly without clicking "Refresh Data".
Adding Headers and Formatting Totals
By default, PIVOTBY handles headers based on heuristic detection. To explicitly configure headers and grand totals for publication-ready executive dashboards:
- Display Column and Row Titles: Pass
3to the headers argument to display full original header labels:=PIVOTBY(A1:A100, B1:B100, C1:C100, SUM, 3) - Remove Grand Totals: If you are embedding the cross-tab into an external chart or secondary calculation where total rows would skew averages, pass
0to the row_total_depth and col_total_depth arguments:=PIVOTBY(A1:A100, B1:B100, C1:C100, SUM, 3, 0, , 0) - Pairing with LET: When referencing long ranges across different sheets, you can also use the LET function in Microsoft Excel to define readable variables for your data ranges.
Sorting Results by Volume and Revenue
You can configure PIVOTBY to sort results automatically so that top-performing regions or product categories rank at the top:
- To sort row headers in descending order based on total revenue, specify
-2in the row_sort_order parameter (referring to the second column of the output matrix). - Example formula:
=PIVOTBY(A2:A100, B2:B100, C2:C100, SUM, 3, 1, -2) - Whenever new transactions are entered, the highest-earning region automatically rises to the first row of your summary table.
Frequently Asked Questions
What is the key difference between PIVOTBY and a traditional Pivot Table?
Traditional Pivot Tables require manual user action or VBA macros to refresh data, can interfere with adjacent spreadsheet cells, and cannot be combined easily with other dynamic array formulas. PIVOTBY is a native formula that updates in real time, responds to filters, and spills dynamically.
Which versions of Excel support the PIVOTBY function?
PIVOTBY is available to Microsoft 365 subscribers across Windows, Mac, and Excel on the Web. It is not available in legacy perpetual standalone releases such as Excel 2019 or Excel 2021.
How do I resolve a #SPILL! error with PIVOTBY?
A #SPILL! error occurs when cells within the calculated output range contain existing text, numbers, or formatting. Clear all data from the cells beneath and to the right of your formula cell to allow the array to expand freely.