MrExcel.com - Your One Stop for Excel Tips & Solutions

Sort Largest Customers to the Top

September 26, 2022 - by

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.

A data set has Subtotals and is collapsed to the #2 view. Choose one cell in the Revenue column and sort Z to A.
Figure 715. 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.

Walmart sorts to the top, but it is not in row 2. It is in row 67. The second largest customer, General Motors is in row 128 followed by Exxon in row 195.
Figure 716. 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 you switch back to #3 view to see the details, you will see that all of the Walmart records were sorted along with the Walmart total. The order of the detail records does not change.
Figure 717. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Eduardo Soares on Unsplash