You want to subtotal data by customer, but see the largest customers at the top and within the customer, see the largest revenue at the top. This requires a pre-sort and a post-sort.
Most people don't realize that you can sort subtotals after collapsing
This sorts an entire group of records into a new position.
But it does not sort within the group.
Jeff from Columbus Indiana offers a simple solution: Sort by Revenue descending before adding subtotals.
Most people don't realize that you can sort subtotals after collapsing
This sorts an entire group of records into a new position.
But it does not sort within the group.
Jeff from Columbus Indiana offers a simple solution: Sort by Revenue descending before adding subtotals.
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2074: Subtotal Pre Sort I have another tip from the road, this one from Columbus, Indiana Seminar, we did there on March 30th, a lot of great seminars coming up: Bloomington, Indianapolis, Sarasota.
This tip is from Jeff.
Now, I usually talk about doing subtotals, right?
So if we have data that's sorted by Date like this and you want to subtotal by Customer, first thing you do is select one cell on the Customer column, Sort A to Z and then out here on the Data tab, use Subtotal, At each change in, Customer.
We’re going to use the SUM function and choose all the numeric fields that make sense, like that.
Click OK and what we get is every time the customer changes, they insert a new row with those subtotals, right?
Really cool.
Now, this next trick I've talked about in the Podcast before, this is from Derrick in Springfield, Missouri.
Once you collapse down to the Number 2 view, if you want the largest customer at the top just choose one cell in the Revenue column and click Z to A, and the largest customer will come to the top.
Cool, right?
But then, when we go back and look to see what happened, if we go back to the Number 3 view, you see that the data within the Customer is not sorted.
It comes in exactly the same situation.
So I sorted by Date, it's in days sequence and a lot of times that's what we want.
But, you might have a manager says, “Well, yeah, I want to see the largest customer at the top but within the largest customer I want to see the largest revenue at the top.” Alright, so Jeff pointed out that he has to do this, his manager asked for this.
And he says it’s simple.
Before you add the subtotals just sort by Revenue like a Z to A, that way the largest customers at the top.
Then come back, sort by Customer, Subtotal, At each change in Customer.
We use the SUM function, choose those numeric fields, click OK.
Collapse it down on the Number 2 view, still use one cell in the Revenue column, Z to A, and now, Wag More Dog Store in San Antonio comes to the top as the largest customer but when we go look within that, it will be sorted correctly.
A couple of cool tricks there.
I love Derrick’s trick of sorting subtotals when they're collapsed and then now Jess trick to get the data with the end sorted as well.
All these tricks are in this book, Power Excel with MrExcel, the 2017 Edition.
Click the “i” up there in the top-right hand corner to get to that.
Wrap-up of this episode: Most people don't realize you can sort the subtotals after collapsing.
It sorts an entire group of records into a new position but does not sort within the group.
Jeff from Columbus, Indiana offers a simple solution: Sort by Revenue, descending before adding the subtotals.
I want to thank Jeff for showing up in my seminar and offering me that trick.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
This tip is from Jeff.
Now, I usually talk about doing subtotals, right?
So if we have data that's sorted by Date like this and you want to subtotal by Customer, first thing you do is select one cell on the Customer column, Sort A to Z and then out here on the Data tab, use Subtotal, At each change in, Customer.
We’re going to use the SUM function and choose all the numeric fields that make sense, like that.
Click OK and what we get is every time the customer changes, they insert a new row with those subtotals, right?
Really cool.
Now, this next trick I've talked about in the Podcast before, this is from Derrick in Springfield, Missouri.
Once you collapse down to the Number 2 view, if you want the largest customer at the top just choose one cell in the Revenue column and click Z to A, and the largest customer will come to the top.
Cool, right?
But then, when we go back and look to see what happened, if we go back to the Number 3 view, you see that the data within the Customer is not sorted.
It comes in exactly the same situation.
So I sorted by Date, it's in days sequence and a lot of times that's what we want.
But, you might have a manager says, “Well, yeah, I want to see the largest customer at the top but within the largest customer I want to see the largest revenue at the top.” Alright, so Jeff pointed out that he has to do this, his manager asked for this.
And he says it’s simple.
Before you add the subtotals just sort by Revenue like a Z to A, that way the largest customers at the top.
Then come back, sort by Customer, Subtotal, At each change in Customer.
We use the SUM function, choose those numeric fields, click OK.
Collapse it down on the Number 2 view, still use one cell in the Revenue column, Z to A, and now, Wag More Dog Store in San Antonio comes to the top as the largest customer but when we go look within that, it will be sorted correctly.
A couple of cool tricks there.
I love Derrick’s trick of sorting subtotals when they're collapsed and then now Jess trick to get the data with the end sorted as well.
All these tricks are in this book, Power Excel with MrExcel, the 2017 Edition.
Click the “i” up there in the top-right hand corner to get to that.
Wrap-up of this episode: Most people don't realize you can sort the subtotals after collapsing.
It sorts an entire group of records into a new position but does not sort within the group.
Jeff from Columbus, Indiana offers a simple solution: Sort by Revenue, descending before adding the subtotals.
I want to thank Jeff for showing up in my seminar and offering me that trick.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
