Excel PIVOTBY Function in 2026: The Ultimate Guide to Dynamic Pivot Analysis Without Pivot Tables

For years, Excel users relied on PivotTables to summarise and group data. They are powerful, but they come with friction: manual refresh, rigid layouts, and the need to click through multiple dialogs. In 2026, the PIVOTBY function has changed the game entirely. With a single formula, you can create dynamic, auto-refreshing pivot-style summaries that live directly in your worksheet and respond instantly to changing data.

Whether you are a financial analyst building reporting dashboards, an operations manager tracking KPIs, or a data enthusiast wanting more flexibility, mastering PIVOTBY will fundamentally change how you work with Excel.

What Is the PIVOTBY Function?

PIVOTBY is a dynamic array function that aggregates data across multiple dimensions simultaneously. It groups rows by one set of fields and columns by another, applying an aggregate function (like SUM, AVERAGE, COUNT, MIN, MAX) at the intersection. The result spills automatically into your worksheet and updates in real time as the underlying data changes.

The basic syntax is: =PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [col_total_depth], [filter_array])

Understanding the Arguments

row_fields

The column or columns from your data range that you want to use as row labels. For example, if you want to see sales by Region and then by Product, you would pass both columns here.

col_fields

The column whose unique values become the column headers of your pivot output. Commonly used for time periods such as quarters or months, or for categories like product tier.

values

The column containing the data you want to aggregate — for example, a Revenue column or a Units Sold column.

function

The aggregation function to apply. You can use SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, MEDIAN, STDEV, and more. You reference these using the function name as text in quotes or via the built-in function references.

Optional arguments

field_headers: Controls whether headers from your data are included in the output (0 = none, 1 = no headers displayed, 2 = headers displayed, 3 = headers in the output).

row_total_depth: Controls whether row totals are shown (0 = none, 1 = grand total, 2 = subtotals and grand total).

col_total_depth: Same as above but for column totals.

filter_array: A boolean array or expression that filters the source data before aggregation.

Your First PIVOTBY Formula

Suppose you have a sales table with columns: Date, Region, Product, Revenue. To summarise total Revenue by Region (rows) and Quarter (columns), your formula would look like:

=PIVOTBY(SalesData[Region], TEXT(SalesData[Date],"Q"&ROUNDUP(MONTH(SalesData[Date])/3,0)), SalesData[Revenue], SUM)

This formula groups rows by Region, creates column headers for each quarter (Q1, Q2, Q3, Q4), and sums Revenue at each intersection. No PivotTable dialog, no manual refresh. The result updates automatically whenever the source data changes.

Multi-Level Row Grouping

One of the most powerful aspects of PIVOTBY is multi-level grouping. To see Revenue broken down by Region and then by Product within each region, pass both columns as an array:

=PIVOTBY(SalesData[[Region]:[Product]], TEXT(SalesData[Date],"YYYY-MM"), SalesData[Revenue], SUM)

This creates a hierarchical row structure showing each Region with its Products nested beneath, and monthly columns across the top. This is functionality that previously required a PivotTable plus grouping settings.

Filtering with PIVOTBY

The filter_array argument makes PIVOTBY extremely flexible. To limit your pivot to only rows where the Status column equals 'Completed':

=PIVOTBY(data[Region], data[Quarter], data[Revenue], SUM,,,,data[Status]="Completed")

You can also combine multiple conditions using boolean logic: (data[Status]="Completed")*(data[Year]=2026). This multiplies two boolean arrays together, effectively creating an AND condition.

Using PIVOTBY with GROUPBY

PIVOTBY works best for two-dimensional summaries. When you only need one dimension, use GROUPBY instead — it uses the same syntax but without the col_fields argument. These two functions were designed as a complementary pair and cover the vast majority of aggregation scenarios that previously required PivotTables.

Formatting PIVOTBY Output

Because PIVOTBY output is a spilled array, you cannot apply cell-by-cell formatting the same way as a PivotTable. However, you can:

Apply a table style to the range after the formula has spilled, being careful not to overwrite the spill range.

Use conditional formatting on the spill range to highlight values above or below a threshold.

Wrap the PIVOTBY inside TEXT or custom number format functions for specific column types.

Reference specific cells in the spill range for charts and downstream calculations using standard cell references.

PIVOTBY vs Traditional PivotTables

Both tools have their place. PIVOTBY wins when you need a formula-driven, auto-refreshing summary embedded in a calculation chain. PivotTables win when you need heavy interactivity, slicers, or drill-down capabilities for non-technical users. In 2026, many Excel power users keep a PivotTable for ad-hoc exploration and PIVOTBY formulas for fixed reporting views.

Common Errors and Fixes

SPILL! error: Another value is blocking the spill range. Clear the cells below and to the right of the formula.

CALC! error: Often caused by mismatched array sizes. Ensure all input ranges are the same length.

VALUE! error: Usually caused by passing text into the values argument where numbers are expected. Check for mixed data types in your values column.

Conclusion

PIVOTBY is one of the most significant additions to Excel's function library in recent years. It brings the power of PivotTable-style analysis directly into the formula bar, making your summaries dynamic, portable, and easy to embed in any dashboard or report.

Invest an hour practising PIVOTBY with your own data, and you will quickly find yourself reaching for it instead of the Insert PivotTable button. Your Excel reporting workflow in 2026 deserves this upgrade.

Start simple with a two-field PIVOTBY, then gradually add multi-level grouping and filter conditions. You will be amazed how much complexity you can handle with a single, elegant formula.