Sort Largest Customers to the Top
September 26, 2022 - by Bill Jelen

Problem: I added subtotals to a data set and collapsed to the #2 view. Now, my manager wants the largest customers at the top of the data set.
Strategy: You would never expect this to work, but you can sort groups of records when in the #2 view.
Start with a the original data set shown here. Choose one cell in the revenue column. Click the ZA button to sort descending.

Wal-Mart comes to the top of the data set, but notice that the Wal-Mart total is in row 67.

Click the 3 Group and Outline button. You will see that all of the Wal-Mart records were sorted along with the Wal-Mart total.

Gotcha: Excel sorts the 65 Wal-Mart records as a single group. It does not perform any sorting within that group. The Wal-Mart detail records are in their original sequence. If you had wanted the detail records sorted descending, you would have originally sorted by Customer ascending, Revenue descending, then added the subtotals.
If you collapse back to the #2 group and sort by Customer, Excel is smart enough to leave the Grand Total at the bottom instead of sorting it into the G’s.
This article is an excerpt from Power Excel With MrExcel
Title photo by Eduardo Soares on Unsplash