- Excel Book Excerpt
Excel Present a Pivot Table in High-to-Low Order by Revenue
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
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.
Figure 826 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.
Figure 827 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.
Figure 828 Choose descending by Revenue.
Results: The report will be sequenced with the largest customers at the top.
Figure 829 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.
Figure 830 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.
Figure 831 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.
For more resources for Microsoft Excel: