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

https://youtu.be/AFrzpEfdWlA

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.

GROUPBY

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.

PIVOTBY

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.

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.

Workaround

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.

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.

Workaround

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.

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.

Workaround

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.

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).

Workaround

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.

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.

Workaround

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.

Conclusion

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.