MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Sort Subtotals


April 03, 2019 - by Bill Jelen

Excel Sort Subtotals. Photo Credit: Nathan Lindahl at Unsplash.com

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.

Columns for Sales Rep, Date, Customer, Quantity, Revenue, Cost, and Profit. The data is sorted by Customer (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, as shown here.


The Subtotal dialog box says: At Each Change in Customer, Use Function Sum, Add Subtotal To Quantity, Revenue, Profit. The boxes for Replace Current Subtotals and Summary Below Data are checked. The box for Page Break Between Groups is unselected. There are three buttons at the bottom of the dialog: Remove All, OK, and Cancel. To add the subtotals, click OK.

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.

Subtotals have been added. You can see a total row for CPASelfStudy.com Total has been inserted after the records for that customer. Similar totals are added throughout the data. But this screenshot is pointing out that three new Group and Outline buttons have been added to the left of the grid. The buttons are labeled 1, 2, and 3. An arrow is telling you to click the #2 Group and Outline button.

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.

After clicking the #2 Group and Outline button, all you see are the Subtotal rows and the Grand Total at the bottom. The customers, however, are still arranged in Alphabetical sequence.

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.

Still in #2 view, the report is still showing one line per customer. But the largest customer is at the top.

If you go back to the #3 view, you will see the detail records that came along with the subtotal row. Excel did not rearrange the detail records; they remain in their original sequence.

Going back to the #3 view, you can see that the detail records were sorted along with the subtotal row.

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.

Title Photo: Nathan Lindahl at Unsplash.com


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.