PIVOTBY A Dynamic PivotTable Alternative

Excel’s PIVOTBY function allows you to create dynamic summary reports that look very much like PivotTables. However, they are totally dynamic … no need to click Refresh. They are created with a single formula, but do not have the rich features and capabilities of PivotTables. Depending on your workbook, the PIVOTBY function may be a nice alternative to a traditional PivotTable.

Video

Note: the PIVOTBY function is NOT available in all Excel versions. At the time I’m writing this, it is being rolled out to Excel 365 users. Also, there is a similar function called GROUPBY which has fewer arguments and options.

Objective

The goal of this tutorial is to familiarize you with the PIVOTBY function in Excel and provide detailed step-by-step instructions on how to use it. By the end of this tutorial, you will be able to create dynamic summary reports using the PIVOTBY function, understand different aggregate functions, explore additional arguments, and customize your reports to suit your needs.

Exercise 1: Creating a Basic Pivot Style Report

Here is a subset of the data we are trying to summarize (Table1):

We’d like to view the total amount by Rep, with Item’s in columns. If we were using a traditional PivotTable, we’d place the Rep field into the Rows layout area, the Item field into the Columns layout area, and the Amount into the Values area. The resulting PivotTable would basically look like this:

Now, let’s generate that same basic report using the PIVOTBY function in a single formula.

The first 4 arguments will feel very intuitive, since they mimic PivotTable fields:

=PIVOTBY(row_fields, col_fields, values, function)

• row_fields – column(s) to display in report rows
• col_fields – column(s) to display across columns
• values – column(s) to aggregate and use as the report values
• function – aggregate function, like SUM, AVERAGE, and so on

So, we can generate our summary report by writing the following formula:

=PIVOTBY(Table1[Rep], Table1[Item], Table1[Amount], SUM)

Bam:

Wow! Except for the cell fill and formatting, this report looks A LOT like our PivotTable!!!!

What is really nice about this report is that, like other formulas, it is automatically updated when dependent cell values change. Meaning, no need to click a Refresh button.

But … we are just getting warmed up, so let’s head to the next exercise.

Exercise 2: Using Additional Aggregate Functions

Let’s explore different aggregate functions that can be used with PIVOTBY.

Here is a subset of the data we’d like to summarize (Table2):

We’d like to see total Amount by Item, with Regions across columns. We do so by writing the following formula:

=PIVOTBY(Table2[Item], Table2[Region], Table2[Amount], SUM)

Hit Enter … and bam:

Let’s talk about the 4th argument … the function argument. We can select one of the built-in aggregate lambda functions, technically known as eta reduced lambda functions, including SUM, MIN, MAX, COUNT, AVERAGE, MEDIAN, COUNT, COUNTA, PERCENTOF, and more. We can also write a custom lambda function here if needed.

So, let’s say we wanted to show the percent of the grand total. Instead of using SUM as the function argument, we can use PERCENTOF as follows:

=PIVOTBY(Table2[Item], Table2[Region], Table2[Amount], PERCENTOF)

After formatting the values as percentages … bam:

So, depending on what you are working on, you have many ways to aggregate the values.

In addition to the first 4 arguments discussed, there are several additional arguments that enable us to customize the report layout.

Exercise 3: Exploring Arguments

The PIVOTBY function offers additional arguments to customize your reports further. Here is a brief description of the PIVOTBY arguments:

• row_fields – column(s) to list in rows
• col_fields – column(s) to list in columns
• values – column(s) to aggregate as report values
• function – aggregate function used to summarize values
• [field_headers] – options for displaying column labels
• [row_total_depth] – options for subtotals and grand totals
• [row_sort_order] – options for how to sort rows
• [col_total_depth] – options for column totals and subtotals
• [col_sort_order] – options for how to sort columns
• [filter_array] – filter which data source rows flow into the report

So, depending on what you are working on, you’ll want to explore these.

Notice that the function supports multiple row/column/value fields. We could simply select multiple columns for each argument. But in practice, it can be more convenient to use the CHOOSECOLS function. That way, the source column order doesn’t matter and we can pick and choose columns as desired.

For example, in Table2 above, Region is the 4th column and Rep is the first column. If we wanted to use two row_fields to group by Region and then by Rep, we can nest CHOOSECOLS into our PIVOTBY function. Since we don’t need any column fields, we’ll leave the col_field argument empty. So we can write the following formula:

=PIVOTBY(CHOOSECOLS(Table2,4,1), , Table2[Amount], SUM)

We hit Enter, and bam:

As you can see, there is quite a bit of functionality to unpack with the PIVOTBY function. We have just scratched the surface in this introductory article. So, depending on what you are working on, you’ll definitely want to spend some time exploring.

Conclusion

Hopefully, this tutorial has provided a basic introduction of PIVOTBY. Even if your current version of Excel doesn’t support PIVOTBY, this post can at least let you know it is out there and what it does so that when you get access to it you’ll be ready to use it.

If you have any suggestions, alternatives, or questions, please share by posting a comment below … thanks!

Sample File

FAQ

Q: Can I use the PIVOTBY function in older versions of Excel?

A: The PIVOTBY function is relatively new and is currently available in Excel 365 and unavailable in older versions of Excel. However, I hope this tutorial helps you understand its operation so you can use it when you do get access to it.

Q: Can I use PIVOTBY on large datasets?

A: PIVOTBY can be used to summarize data that exists in a worksheet, so it is limited to the number of worksheet rows (about 1M rows).

Q: Can I combine PIVOTBY with other Excel functions?

A: Yes, PIVOTBY can be combined with other Excel functions such as CHOOSECOLS as demonstrated in Exercise 3.

Q: What are some alternatives to the PIVOTBY function in Excel?

A: If you are using an older version of Excel that does not support the PIVOTBY function, you can still create similar summary reports using traditional PivotTables or using formulas like SUMIFS, COUNTIFS, and AVERAGEIFS.

Q: Can the PIVOTBY function handle multiple data sources?

A: No, the PIVOTBY function is designed to work with a single data source. If you need to combine data from multiple sources, you would need to consolidate the data into a single table before using the PIVOTBY function … and Power Query would be a great option to do that.

Q: Can I customize the formatting of the PIVOTBY report?

A: Yes, you can customize the formatting of the PIVOTBY report just like any other Excel worksheet. You can change the font, color, alignment, and apply conditional formatting to highlight specific values or cells.

Q: Can I use the PIVOTBY function to create charts and graphs?

A: You can create a chart based on the results of the PIVOTBY function.

Q: Can I use the PIVOTBY function with non-numeric data?

A: Yes, the PIVOTBY function can handle non-numeric data. You can aggregate text values using functions like COUNTA or ARRAYTOTEXT.

Q: Can I filter the data in the PIVOTBY report?

A: Yes, you can filter the data in the PIVOTBY report using the filter_array argument. You can specify criteria to include specific rows of data from the report.

Q: Can I use the PIVOTBY function with external data sources?

A: No, the PIVOTBY function can only be used with data that is stored within the Excel workbook. If you want to work with external data sources, you would need to import into Excel … and Power Query would be a great option for that.

Q: Can I use the PIVOTBY function with named ranges?

A: Yes, you can use named ranges as arguments in the PIVOTBY function. Named ranges can make your formulas more readable and easier to maintain.