MrExcel.com - Your One Stop for Excel Tips & Solutions

Present a Pivot Table in High-to-Low Order by Revenue

December 22, 2022 - by

Present a Pivot Table in High-to-Low Order by Revenue

Problem: A pivot table organizes data alphabetically by default. I want to produce a report that is sorted high to low by revenue.

Customers appear alphabetically in the pivot table. Open the drop-down menu on Customer (or Row Labels if your pivot table is in Compact layout)
Figure 886. Reports are normally sorted alphabetically.

Strategy: Each pivot table field offers a sort option. To access the sort options for a field, follow these steps:

  • 1. Open the Customer field dropdown in cell A3. Gotcha: Depending on the layout, this field might be called Row Labels instead of Customer.

  • 2. Choose More Sort Options.

Choose More Sort Options...
Figure 887. Choose More Sort Options.

  • 3. Excel displays the Sort (Customer) dialog. Initially, the sort is set to Manual. This option lets you re-sequence items by dragging or retyping as discussed in the previous topic. Choose Descending. Open the dropdown under Descending and choose Revenue.

In the Sort (Customer) dialog, choose Descending by Revenue.
Figure 888. Choose descending by Revenue.

Results: The report will be sequenced with the largest customers at the top.

The pivot table now has the largest customers at the top.
Figure 889. Largest customers at the top.

Further, as you continue to pivot this report, Excel will remember that customers should always be sorted based on descending revenue. In this figure, product is added as an outermost row field. The report is automatically sorted, this time with Exxon at the top.

Once you set up the rule for sorting customer in descending revenue, that rule will stick as long as customer is in the pivot table. Here, Product is added as an outer row field and within Product ABC, the customers are sorted descending.
Figure 890. Customer continues to re-sort after pivoting.

Additional Details: If you use the Compact Form layout with multiple row fields, there is an extra step. When you open Row Labels, you have to choose from a second dropdown to choose which field you want to sort.

In a pivot table that is both in Compact Layout and had multiple row fields, you first open the Row Labels drop-down and then have to choose the appropriate field from the list.
Figure 891. Extra dropdown in Compact Form layout.

An alternate method for accessing the Sort dialog is to hover over the Customer field in the top of the PivotTable Field List dialog. A dropdown appears. You can choose to sort or filter from this dropdown.

This article is an excerpt from Power Excel With MrExcel

Title photo by Glen Carrie on Unsplash