How to Use PIVOTBY in Excel

The new PIVOTBY function in Excel is a game-changer for anyone who summarizes and analyzes data. It takes the power of a traditional PivotTable and puts it directly into a dynamic array formula, giving you a fast, flexible, and refresh-free way to create summary reports. This article will walk you through exactly how PIVOTBY works, from basic syntax to advanced examples.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly is the PIVOTBY Function?

PIVOTBY is a new function available in Microsoft 365 that allows you to aggregate data with both row and column groupings. Think of it as a formula-based version of a PivotTable. You tell it which columns from your source data you want to use for rows, which for columns, what values you want to summarize, and how you want to summarize them (e.g., using SUM, AVERAGE, COUNT).

Unlike traditional PivotTables that require you to drag fields and manually refresh, PIVOTBY is a dynamic array formula. This means two things:

1. It lives in a single cell, and the results automatically "spill" into the surrounding empty cells.
2. It recalculates instantly whenever your source data changes, no refresh button needed.

If you're familiar with the GROUPBY function, PIVOTBY is its powerful sibling. While GROUPBY creates a one-dimensional summary (grouping only by rows), PIVOTBY creates a two-dimensional crosstab summary (grouping by both rows and columns).

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Understanding the PIVOTBY Syntax

At first glance, the formula might seem intimidating with its ten arguments, but most are optional. Once you break it down, it's quite logical.

Here’s the full syntax:

=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array])

Let's go through each part:

• row_fields: The column(s) you want to use to group your data into rows. This is mandatory.
• col_fields: The column(s) you want to use to group your data into columns, creating the top headers. This is also mandatory.
• values: The column of numbers you want to aggregate or calculate. Mandatory.
• function: The calculation you want to perform on the values. This will typically be a simple function like SUM, AVERAGE, COUNTA, MAX, or MIN. Mandatory.
• [field_headers] (Optional): Tells Excel if your data has headers and if you want to display them. Use 3 to indicate your data has headers and you want them shown in the output.
• [row_total_depth] (Optional): Controls whether Grand Totals and Subtotals appear for the rows. Use 1 for Grand Totals only.
• [row_sort_order] (Optional): An integer indicating how to sort the rows. Use 1 to sort by the first row field in ascending order, or -1 for descending.
• [col_total_depth] (Optional): Same as row_total_depth, but for the columns. Use 1 for Grand Totals only.
• [col_sort_order] (Optional): Same as row_sort_order, but for the columns.
• [filter_array] (Optional): A logical expression (e.g., Region="East") that returns a list of TRUE/FALSE values, filtering the source data before pivoting.

A Simple PIVOTBY Example: Sales by Product and Month

The best way to understand PIVOTBY is to see it in action. Imagine you have a simple sales data table like this:

Let’s say our data is in cells A1:D7. Our goal is to create a summary with the Category as rows, the Month as columns, and the sum of Sales populating the table.

Here is the formula using only the required arguments:

=PIVOTBY(B2:B7, A2:A7, D2:D7, SUM)

• B2:B7 is our row_fields (Category).
• A2:A7 is our col_fields (Month).
• D2:D7 is our values to aggregate (Sales).
• SUM is our calculation.

When you enter this formula into a single cell, Excel automatically generates the following summary table:

This is a fully dynamic report. If you change a sales number in the source data, the summary table will update instantly without any extra effort.

Advanced PIVOTBY Techniques

The real power of PIVOTBY comes from its optional arguments. Let’s build upon our previous example to explore some more advanced capabilities.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

1. Adding Headers and Grand Totals

Our simple report is missing headers and totals, which makes it harder to read. We can fix this by adding the field_headers and total_depth arguments. Let’s update our formula to include the data headers (A1:D7) and add grand totals.

=PIVOTBY(B1:B7, A1:A7, D1:D7, SUM, 3, 1, , 1)

• Updated ranges to start from row 1 to include headers.
• 3 for field_headers to show headers.
• 1 for row_total_depth to add Grand Total row.
• 1 for col_total_depth to add Grand Total column.
• Left row_sort_order blank by adding a comma without a value.

Now our output is much more robust and professional, complete with labels and totals.

2. Filtering Data with filter_array

The filter_array argument is one of the most useful features. It lets you create complex, filtered reports that would otherwise require multiple steps. For example, to see only sales for the 'North' region:

=PIVOTBY(B1:B7, A1:A7, D1:D7, SUM, 3, 1, , 1, , C1:C7="North")

Here, C1:C7="North" generates a TRUE/FALSE array. The function runs its aggregation only on rows where this is TRUE. The result is a pivot showing sales data exclusively for North.

You can create more complex conditions, e.g., filtering for sales over $4000 in North:

(C1:C7="North")*(D1:D7>4000)

Multiplying logical arrays acts like an AND condition.

3. Sorting Your Pivoted Data

By default, PIVOTBY sorts row and column labels in ascending order. To reverse the order, set the sort_order arguments. For descending order on categories:

=PIVOTBY(B1:B7, A1:A7, D1:D7, SUM, 3, 1, -1, 1)

The -1 in position 7 sorts the first row field (Category) in descending order.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

PIVOTBY vs. Traditional PivotTables

Should you abandon PivotTables? Not necessarily. Both have their place.

Key Advantages of PIVOTBY:

• Real-Time Updates: Formulas recalculate instantly. No need to click "Refresh."
• Transparency: The logic is visible in the formula bar.
• Integration: Can nest inside other dynamic array functions like SORT or FILTER.
• Lightweight: No cache or file bloat.

Key Advantages of Traditional PivotTables:

• User-Friendly UI: Drag-and-drop interface.
• Interactive Features: Slicers, timelines, drill-downs.
• Advanced Calculations: Built-in options like "% of Grand Total."
• Formatting: Preset styles.

Ultimately, choose based on your need for an instantly updating, transparent report or an interactive dashboard.

Final Thoughts

The PIVOTBY function is a fantastic addition to Excel's analytical toolkit, offering a powerful, formula-based method for creating two-dimensional data summaries. It empowers you to build dynamic and transparent reports that update in real time, sidestepping the need for manual refreshes and clunky user interfaces.

While mastering functions like PIVOTBY in Excel is valuable, building reports can still be manual and tedious, especially with data spread across platforms like Google Analytics, Shopify, or CRMs. At Graphed, we automate this workflow. You connect your data sources in a few clicks and create real-time dashboards and reports via plain English—no complex formulas needed. It’s like having a data analyst on your team, turning hours of work into quick, 30-second conversations.