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.
- 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.
- 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.
- 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.