5 Problems with Excel’s New PIVOTBY Function

Bottom Line: Learn five issues and workarounds for the new PIVOTBY function recently introduced by Microsoft.

Skill Level: Intermediate

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Download the Excel File

You can access the Excel file used in the video for free by clicking below.

Excel's New PIVOTBY Function

Microsoft recently introduced two new functions for Excel: GROUPBY and PIVOTBY. These functions allow users to create summary reports using formulas. While these new features offer exciting possibilities, they come with their own set of challenges. In this blog post, we'll look into five problems with Excel's new PIVOTBY function and explore potential workarounds.

First, let's take a quick look at how they work.


The GROUPBY function allows you to create a summary of the data you choose to see. In the example below, I've chosen to sum the quantities from rows that have the same color.

GROUPBY Function Explained


PIVOTBY is similar, but it adds an additional option for column fields. In this example, I've added Years as my column fields, so that the quantities by color are broken out into the various years they were sold.

PIVOTBY adds column fields

These two new functions are awesome, especially because any changes you make to the data are immediately reflected in the tables. With Pivot Tables, you must refresh the table whenever you make data changes. If you are not familiar with Pivot Tables, you can start learning about them with this tutorial: How Do Pivot Tables Work?

Although the automatic refresh is a pretty sweet benefit of GROUPBY and PIVOTBY, these functions present some problems when compared to Pivot Tables, in my opinion. Here are 5 of those problems and their workarounds.

1. Formatting Challenges

When comparing PIVOTBY to traditional Pivot Tables, one noticeable difference is in formatting options. Pivot Tables offer a variety of styles and layout options through the Design tab, making it easy to customize the appearance of the report. However, PIVOTBY lacks these formatting options, requiring manual formatting of headers and totals. Furthermore, formatting in PIVOTBY is not dynamic, meaning changes in data may not reflect in the formatting automatically.

PIVOTBY vs Pivot table


Conditional formatting can be used to dynamically format PIVOTBY reports. By setting conditional formatting rules based on specific criteria, such as the presence of Total rows, we can ensure consistent formatting even as data changes.

Conditional Formatting Rules Manager

If you don't have much experience with conditional formatting, check out this tutorial on How to Apply Conditional Formatting to Rows Based on Cell Value.

2. Limited Flexibility

Traditional Pivot Tables are renowned for their flexibility, allowing users to quickly pivot or rearrange the report structure with simple drag-and-drop actions. In contrast, PIVOTBY lacks this flexibility, making it challenging to modify report layouts quickly.

Pivot Table Fields Task Pane


A workaround involves creating a PIVOTBY Builder tool, which offers a more user-friendly interface for selecting row and column fields. While it may not replicate the drag-and-drop functionality of Pivot Tables, it provides a more structured approach to modifying PIVOTBY reports.

PIVOTBY Builder Tool

Since the formulas for this Builder can get pretty complicated, I recommend copying it from the Excel file included at the top of this tutorial rather than building it yourself.

3. Absence of Pivot Charts and Slicers

Another drawback of PIVOTBY is the absence of built-in support for pivot charts and slicers. Pivot Tables allow users to easily create visualizations and add interactivity through charts and slicers, like the ones pictured below, enhancing the presentation and usability of reports.

Pivot Table Charts and Slicers
Click to enlarge


One workaround is to use a Dynamic Grid Slicer, which leverages checkboxes and formulas to mimic slicer functionality in PIVOTBY reports. While not as seamless as built-in slicers, Dynamic Grid Slicers offer a formula-based alternative for filtering data.

Dynamic Grid Slicer for PIVOTBY tables

Here's a tutorial that explains the Dynamic Grid Slicer in more detail: New Excel Slicer for PIVOTBY.

4. Challenges with Totals and Subtotals

Controlling totals and subtotals in PIVOTBY can be challenging, as the options for customizing these calculations are limited compared to traditional Pivot Tables. Additionally, subtotal columns may not be clearly identifiable, making it difficult to distinguish them from regular data.

You can see the difference by comparing the two images below (PIVOTBY shown on top and Pivot Table on bottom).

PIVOTBY Summary Report

Pivot Table Summary Report


Conditional formatting can once again come to the rescue by applying formatting rules to subtotal columns, making them visually distinct from other data. By setting conditional formatting based on specific criteria, such as the presence of a blank cell in the subtotal columns, we can add the word “Total” to the subtotal and fill those columns with gray color to mimic what we are accustomed to seeing with Pivot Tables.

Here's what our PIVOTBY table looks like with the conditional formatting.

PIVOTBY with Conditional Formatting

5. Limited Options for % of Totals

While PIVOTBY offers the option to calculate percentages of column totals, it lacks the flexibility of traditional Pivot Tables, which also allow for percent of row total calculations. This limitation may impact the types of analysis users can perform directly within PIVOTBY reports.

Percent of Row Total


To calculate percent of row totals in PIVOTBY, we can switch the row and column fields and then apply the percent of column total calculation. By manipulating the report structure and then using the TRANSPOSE function, we can achieve similar results to traditional pivot tables.


In conclusion, while Excel's new PIVOTBY function introduces exciting capabilities for creating formula-based reports, it also comes with its share of limitations. From formatting challenges to flexibility issues, users may find themselves grappling with various aspects of PIVOTBY compared to traditional Pivot Tables.

However, with creative workarounds, we can mitigate these challenges and leverage PIVOTBY more effectively.

Do you see yourself using PIVOTBY and GROUPBY in place of Pivot Tables? Leave a comment and let us know.

1 comment

Your email address will not be published. Required fields are marked *

  • I would much rather use a pivot table rather than the pivotby function. Thank you for your time in making this comparison.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter