Excel Subtotal Sort Magic

Sorting by subtotals is possible

Sometimes Excel surprises me. In this case it sorts in a way I didn’t expect, but in a good way. Thanks to Mr Excel for the tip.

See the image below. It is a list where we have used the Automated Subtotal feature from the Data ribbon.

If you are unfamiliar with the Automated Subtotal check out this post.

If we click the small number 2 in the top left, the display changes to the image below. This shows just the subtotals and the grand total.

This is where things get interesting.

If you want to sort the amount column from largest to smallest for the subtotals amounts you can select the Amount cell C1 and then click the Sort Descending icon in the Data ribbon tab  – see below.

Magically the table changes to put the highest subtotal and all the underlying data at the top – as per the image below.

If we expand out the selection (see image below) you can see that while the subtotals have been sorted the individual values have not been sorted.

If you need the individual amounts sorted too, you would have to apply that sort order before you applied the Automated Subtotals.