Excel: Sort Largest Customers to the Top

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

  1. Collapse the data, sort by revenue.

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

  2. The largest customers come to the top.

    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.

  3. When sorting Wal-Mart to the top, rows 2-67 were treated as a single unit in the sort. This is fairly amazing.

    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.