Sort Excel Subtotals

August 01, 2017 - by Bill Jelen

Thanks to Derek Fraley from Springfield MO for this tip.

This tip is from my friend Derek Fraley in Springfield, Missouri. I was doing a seminar in Springfield, and I was showing my favorite subtotal tricks.

For those of you who have never used subtotals, here is how to set them up.

Start by making sure your data is sorted. The data below is sorted by customers in column C.

From the Data tab, choose Subtotals. The Subtotal dialog box always wants to subtotal by the leftmost column. Open the At Each Change In dropdown and choose Customer. Make sure the Use Function box is set to Sum. Choose all of the numeric fields.

When you click OK, Excel inserts a subtotal below each group of customers. But, more importantly, it adds Group and Outline buttons to the left of column A.

When you click the #2 Group and Outline button, the detail rows are hidden, and you are left with only the subtotal rows and the grand total. This is a beautiful summary of a detailed data set. Of course, at this point, the customers appear in alphabetic sequence. Derek from Springfield showed me that when the data is collapsed in the #2 view, you can sort by any column. In the figure below, a Revenue column cell is selected, and you are about to click the ZA sort button.

The top customer, Wag More Dog Store, comes to the top of the data set. But it does not come to row 2. Behind the hidden rows, Excel actually sorted a chunk of records. All of the Wag More detail rows moved along with the subtotal row.

If you go back to the #3 view, you will see the detail records that came along. Excel did not sort the detail records but brought them in their original sequence.

To me, this is astounding on two fronts. First, I am amazed that Excel handles this correctly. Second, it is amazing that anyone would ever try this. Who would have thought that Excel would handle this correctly? Clearly, Derek from Springfield.

Watch Video

Title Photo: hjy5400 / pixabay