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.
Sort Largest Customers to the Top
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.
Figure 655 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.
Figure 656 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.
Figure 657 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.