The PIVOTBY Function

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

The Excel GROUPBY and PIVOTBY functions Part two of a two-part series of blogs
You may be familiar with using pivot tables in Excel to group and aggregate a list of data. Did you know that you can now do the same thing using simple functions? This blog shows you how to use the new GROUPBY and PIVOTBY functions to quickly summarise data. The GROUPBY Function The PIVOTBY Function (this blog)

The Excel GROUPBY and PIVOTBY functions

Part two of a two-part series of blogs

You may be familiar with using pivot tables in Excel to group and aggregate a list of data. Did you know that you can now do the same thing using simple functions? This blog shows you how to use the new GROUPBY and PIVOTBY functions to quickly summarise data.

The GROUPBY Function
The PIVOTBY Function (this blog)

Posted by Andrew Gould on 01 November 2024

In this blog

Simple Row and Column Groups
Including Field Headers
Sorting the Results
Multiple Calculation Columns
Multiple Row Groups
Multiple Column Groups
Filtering Values
Showing Percentage of Rows or Columns
Summary

One limitation of the GROUPBY function is that you can only group by rows. The PIVOTBY function allows you to group by both rows and columns, essentially allowing you to produce an entire pivot table by writing a single formula! Here's the syntax of the function:

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

Although it looks complicated, you should recognise many of the parameter names from the GROUPBY function - you already know most of what you need to know to use PIVOTBY!

Simple Row and Column Groups

Only the first four parameters of the PIVOTBY function are compulsory:

Parameter	What it means
row_fields	The values you want to group the rows by.
col_fields	The values you want to group the columns by.
values	The values you want to use in the calculation.
function	The function you want to apply to the values.

Here's a reminder of the movie data we're working with:

Sample movie data ready for the Excel PIVOTBY function

So much choice for grouping and aggregating!

The example below groups the data by Source and Certificate, showing the Sum of Run Time Minutes for each group:

A simple example of the PIVOTBY function in Excel

It couldn't be much simpler!

Including Field Headers

You can include field headers in the same way as for the GROUPBY function, although the results are a little messier this time:

The results are, arguably, more difficult to read now.

Sorting the Results

You can sort the rows and columns of the resulting table independently using the row_sort_order and col_sort_order parameters.

Sorting the rows and columns of the PIVOTBY function output.

This sorts both the rows and columns in descending order of the sum of run time using the value -2.

Multiple Calculation Columns

You can include multiple calculation columns in the same way as for the GROUPBY function. The example below includes the average of the Budget Dollars and Box Office Dollars columns:

Each Certificate shows the average for two columns.

You can again use the CHOOSECOLS function to select non-adjacent columns:

Non adjacent columns in the PIVOTBY function in Excel

This example picks columns 1 and 5 from the range D1:H3401.

You can also stack multiple different functions for a single value column:

Multiple functions for one value in Excel's PIVOTBY function

We're using HSTACK to show both the SUM and AVERAGE of Run Time Minutes for each Certificate.

Multiple Row Groups

You can create multiple row groups in the same way as for the GROUPBY function.

We've grouped rows by Genre and Source.

With multiple row groups, you can choose how to display totals and subtotals:

Choosing totals and subtotals for multiple row gropus in PIVOTBY in Excel

We're opting to show grand totals and subtotals for row groups.

You can see the results in the diagram below:

Totals and subtotals for row groups in PIVOTBY in Excel

Each Genre group receives a subtotal and the grand total appears at the bottom of the table.

Multiple Column Groups

As you might expect, you can also apply multiple levels of grouping to the columns, as shown in the example below:

Grouping by multiple columns with Excel's PIVOTBY function

The columns are grouped by Source and Certificate.

You also have the option to display column totals and subtotals:

Choosing totals for columns in Excel's PIVOTBY function

The options are the same as for row totals.

Filtering Values

The filter_array parameter behaves in the same way as for the GROUPBY function:

Filtering the rows in the PIVOTBY function in Excel

This filter includes films which have at least 1 Oscar nomination.

Showing Percentage of Rows or Columns

You can use the PERCENTOF function to show values as a percentage of a total:

Showing percentage of column totals in Excel's PIVOTBY function

We've added some formatting to make the results easier to read.

By default, the results are calculated as a percentage of the column total, but you can use the relative_to parameter to change this:

Changing the percentof formula in Excel's PIVOTBY function

It takes a lot of commas to skip to the relative_to argument!

Here are the results of using the Row Totals option:

Percentage of row totals using the PIVOTBY function in Excel

The values are shown as a percentage of the row total.

Summary

The GROUPBY and PIVOTBY functions provide a lot of power to group and aggregate large datasets incredibly quickly. While they don't replicate all the functionality of a full pivot table I think they're an excellent choice for getting quick answers and I'm looking forward to teaching these new functions in upcoming Excel courses!

Parts of this blog
The GROUPBY Function The PIVOTBY Function (this blog)

Some other pages relevant to the above blogs include:

Microsoft Excel training courses

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR