Sorting a PIVOTBY Report

Hello Excel enthusiasts! Today, I’m answering a question I was recently asked: how to sort a pivot-style formula-based report in descending order by amount. The whole process is easier than it sounds, and I’ll demonstrate the steps through a series of exercises. Don’t worry, if you aren’t sure what a “pivot-style formula-based” report is, I’ll explain that as well. Let’s go!

Video

Task at Hand

Let’s clarify the goal. We have data in a table that we want to summarize. In other words, we want to create a summary report from the detailed data.

Maybe our data looks like this (with multiple transactions for each rep):

And we want to summarize it by Rep ID, so the report looks a bit like this:

But, we want the report to be sorted descending by amount, like this:

Our challenge then is to build and sort this report with a single formula that dynamically and automatically updates when the data table is updated. Yes, this is doable!

Exercise 1 – The Pivot Table Approach

To understand the traditional approach to building such a summary report, let’s start with a PivotTable. Select any cell in the data table and navigate to Insert > PivotTable. In the resulting dialog, we confirm the data range and opt to create the report in an existing (or new) worksheet. We then drag-and-drop the rep ID Field into the Rows layout area and the Amount field into the Values layout area. Bam:

PivotTables can easily be sorted descending by amount. To do so, we use the filter control to select more sort options, define the desired sort, and bam:

While we will certainly continue using PivotTables, a recent Excel update provides another option. We can build the same report by writing a single formula. And that brings us to the next exercise.

Exercise 2- The Formula Based Report

To build a similar report with a formula, we’ll use the PIVOTBY function.

Note: not all versions of Excel support the PIVOTBY function.

The PIVOTBY function simulates a PivotTable, and includes the arguments you might expect, such as defining the row fields, value fields, and aggregate function, among others. Assuming our data table is named Table1, we write the following formula:

=PIVOTBY(Table1[RepID], , Table1[Amount], SUM)

• Table1[RepID] is the row fields (similar to dragging a field into the Row layout area of a PivotTable)
• Empty column fields argument
• Table1[Amount] is the values field (like dragging a field into the Values layout area)
• SUM is the aggregate function to apply to the values field

We hit enter, and bam:

Now that we have covered the main function arguments, let’s discuss the other arguments so we can understand how to sort this report descending by amount.

Exercise 3- Descending Order Challenge

In addition to the expected arguments such a defining the rows, columns, values, and function, there are many additional arguments we can use to customize our report.

According to the Microsoft website which provides more info including which versions of Excel support the function, here are the PIVOTBY arguments:

• row_fields (required) A column-oriented array or range that contains the values which are used to group rows and generate row headers. The array or range may contain multiple columns. If so, the output will have multiple row group levels.
• col_fields (required) A column-oriented array or range that contains the values which are used to group columns and generate column headers. The array or range may contain multiple columns. If so, the output will have multiple column group levels.
• values (required) A column-oriented array or range of the data to aggregate. The array or range may contain multiple columns. If so, the output will have multiple aggregations.
• function (required) A lambda function or eta reduced lambda (SUM, AVERAGE, COUNT, etc) that defines how to aggregate the values. A vector of lambdas can be provided. If so, the output will have multiple aggregations. The orientation of the vector will determine whether they are laid out row- or column-wise.
• field_headers A number that specifies whether the row_fields, col_fields and values have headers and whether field headers should be returned in the results. The possible values are:
  • Missing: Automatic.
  • 0: No
  • 1: Yes and don’t show
  • 2: No but generate
  • 3: Yes and show
  • Note: Automatic assumes the data contains headers based on the values argument. If the 1st value is text and the 2nd value is a number, then the data is assumed to have headers. Fields headers are shown if there are multiple row or column group levels.
• row_total_depth Determines whether the row headers should contain totals. The possible values are:
  • Missing: Automatic: Grand totals and, where possible, subtotals.
  • 0: No Totals
  • 1: Grand Totals
  • 2: Grand and Subtotals
  • -1: Grand Totals at Top
  • -2: Grand and Subtotals at Top
  • Note: For subtotals, row_fields must have at least 2 columns. Numbers greater than 2 are supported provided row_field has sufficient columns.
• row_sort_order A number indicating how rows should be sorted. Numbers correspond with columns in row_fields followed by the columns in values. If the number is negative, the rows are sorted in descending/reverse order. A vector of numbers can be provided when sorting based on only row_fields.
• col_total_depth Determines whether the column headers should contain totals. The possible values are:
  • Missing: Automatic: Grand totals and, where possible, subtotals.
  • 0: No Totals
  • 1: Grand Totals
  • 2: Grand and Subtotals
  • -1: Grand Totals at Top
  • -2: Grand and Subtotals at Top
  • Note: For subtotals, col_fields must have at least 2 columns. Numbers greater than 2 are supported provided col_field has sufficient columns.
• col_sort_order A number indicating how rows should be sorted. Numbers correspond with columns in col_fields followed by the columns in values. If the number is negative, the rows are sorted in descending/reverse order. A vector of numbers can be provided when sorting based on only col_fields.
• filter_array A column-oriented 1D array of Booleans that indicate whether the corresponding row of data should be considered.
  • Note: The length of the array must match the length of those provided to row_fields and col_fields.

So, now we understand how to sort the report descending by amount. We define the row_sort_order argument field as -2. Our updated formula is:

=PIVOTBY(Table1[RepID], , Table1[Amount], SUM, , , -2)

We hit Enter, and bam:

Now, the nice thing about this approach is that whenever the data in Table1 changes, no action is needed by us to update the report. Since it is a formula, it behaves like other Excel formulas and updates automatically when the dependent data changes. Very cool!

Conclusion

We can now create a pivot-style report from a formula that uses the PIVOTBY function. With the numerous function arguments, we are able to customize this report as desired, including the sort order. If you have any questions, suggestions, or alternatives, drop them in the comments below … thanks!

Sample File

To practice these steps, you can download the sample Excel file.

FAQ

Q1. What if we want to sort the report in an ascending order?

A1. Use positive numbers (such as 1) to sort the report in an ascending order.

Q2. How to sort by other columns?

A2. Use the report’s column number in the ‘row sort order’ field.

Q3. Can we use other functions like ‘COUNT’ instead of ‘SUM’ in the report?

A3. Yes, there are many aggregate functions available.

Q4. Can we use more than one row field in the PIVOTBY function?

A4. Absolutely, Excel supports multiple row fields.