How to understand the PIVOTBY() function in Excel

In a previous post, we explored the GROUPBY() function as a formula-driven alternative to PivotTables in Excel:

Although the GROUPBY() function is useful, it lacks a key feature: the ability to add variables across the columns of a PivotTable. In this post, you’ll see how we can overcome this limitation using the PIVOTBY() function in Excel. This function is somewhat more complex than GROUPBY(), but it offers additional flexibility.

You can follow along with the exercise file provided below:

The PIVOTBY() function in Excel requires four essential arguments and offers several optional ones. This function is truly a next-generation feature based on its complexity alone! We will concentrate on the four mandatory arguments and the fifth optional one, which enables us to add labels to our results. For a deeper understanding of the syntax, refer to Microsoft’s official documentation.

The key difference here, compared to using GROUPBY(), is that we can add categories to both rows and columns. Consider the following example using the demo dataset. Here, we use GROUPBY() to categorize the rows and calculate the sum of sales:

=GROUPBY(sales[[#All],[category]], sales[[#All],[sales]], SUM, 3)

If desired, we could achieve the same outcome using the PIVOTBY() function. This time, however, we would leave an argument blank to indicate that no columns are to be included:

=PIVOTBY(sales[[#All],[category]], ,
sales[[#All],[sales]],
SUM, 3)

Now, let’s explore capabilities beyond GROUPBY(). For example, we can flip around our arguments to rearrange the PivotTable to display categories along the columns:

OK, let’s now move on to something more realistic and visually appealing. This time, I’ll position country along the rows and category atop the columns:

=PIVOTBY( sales[[#All],[country]],
sales[[#All],[category]],
sales[[#All],[sales]],
SUM, 3)

PivotTables become especially useful when displaying multiple categories along the rows, allowing for a detailed breakdown by combinations of categories. Additionally, customizing the aggregation types within the PivotTable enables the use of the most illuminative methods for each variable. Let’s delve into constructing a more complex PivotTable next.

=PIVOTBY(sales[[#All],[country]:[customer_gender]],
sales[[#All],[category]],
sales[[#All],[quantity]:[sales]],
HSTACK(AVERAGE, SUM), 3)

In this example, I placed both country and customer_gender along the rows, and “category” across the columns. I even specified that I wanted an average of quantity and a sum of sales using the HSTACK() function.

One annoying thing about creating a PivotTable-like output here, as opposed to using an actual PivotTable, is the lack of a menu to assist with formatting. You could certainly format this manually, but if you’re looking for something more responsive and enjoyable to build, consider adding some conditional formatting. I set up a rule to format the data based on whether row 4 indicates quantity or sales:

It’s also worth noting that referencing multiple columns simultaneously is simpler when those columns are adjacent in the PivotTable. While not essential, this proximity simplifies the process. If the columns are not adjacent, you can utilize the CHOOSECOLS() function:

One particularly cool application of the PIVOTBY() function is the ability to create a quick two-way proportion table using the new PERCENTOF() function:

=PIVOTBY(sales[[#All],[country]],
sales[[#All],[category]],
sales[[#All],[sales]],
PERCENTOF, 3)

You even have the option here to change how the proportions are calculated in this table.

For example, instead of basing the proportions on the grand total, you can calculate them across the row totals. This requires modifying the very last of so many function arguments in this function. It looks pretty unusual, doesn’t it?

=PIVOTBY(sales[[#All],[country]],
sales[[#All],[category]],
sales[[#All],[sales]],
PERCENTOF, 3, , , , , , 1)

I hope this post got you excited about the power of PIVOTBY(). There is so much you can achieve with IT, and I encourage you to explore its many parametes.

This function offers a formula-driven, instantly updated approach to creating PivotTable-like solutions. Nice work for getting the hang of it — remembering how to perform these operations can even be mentally taxing to programmers in languages like Python or R.

If you’re seeking an introduction to dynamic array functions in Excel, consider exploring my book, Modern Data Analytics in Excel:

For corporate training solutions to help your team maximize their use of Excel for data analytics, check out my Modern Data Analytics in Excel workshops: