After adding subtotals by customer in Excel, can you sort the largest customer to the top? Yes, you can. Recap:
Thanks to Derek Fraley from Springfield MO for this tip
Add Subtotals
Use Group and Outline button to show only subtotals
Sort descending by revenue
Excel sorts chunks of records
Sort again by customer
Grand total goes back to the bottom
Thanks to Derek Fraley from Springfield MO for this tip
Add Subtotals
Use Group and Outline button to show only subtotals
Sort descending by revenue
Excel sorts chunks of records
Sort again by customer
Grand total goes back to the bottom
Transcript of the video:
Learn Excel podcast, episode 1994 - Sort Excel Subtotals!
Go ahead and subscribe to the playlist, I'll be podcasting this entire book.
Welcome back to MrExcel netcast, I'm Bill Jelen.
Today we're talking about subtotals, a great feature that's been around since Excel 97!
I have data here, I make sure to sort the data by customer, hundreds of rows of data, lots of customers.
When I go into Data, and then Subtotal, Add each change in: , they always want to subtotal by the leftmost column, you can change that in: Customer.
They offer 11 functions here - sum, count, average, as long as your rightmost column is numeric, we are going to choose the three columns here, or if you want every customer on their own page, page break between groups, click OK.
And then what we get is, every time the customer changes, we get a new subtotal row that was added.
At the very bottom we even get a grand total.
Alright, that grand total is hard to do, but, thanks to the subtotal function.
But over here on the left-hand side we have three new buttons: grouped outline buttons, that #2 view.
That is the beautiful view of the data.
Alright so, I was in Springfield, MO, and someone asked me is there a way to sort the subtotals, so that the largest customer is at the top.
Alright, and I started to think about this, and it's like, each of these customers has several records associated with it, If I would somehow sort this and get the largest value to come to the top, well, you know it would be a formula pointing at rows above us, and it's not there anymore, but Derek was back there on row 6, Derek says "Hey, no, wait, you can do this, just choose one cell in the revenue column, and click ZA." I'm like "Derek, I don't think that'll work, there's no way that'll work." Derek says "Trust me, just try it." Alright so here I'm going to sort this, and "Wag More Dog Store, San Antonio" comes to the top, but they don't come to row 2, it comes to row 49.
And if we go look at the #3 view, which shows us all the detail, you see that Excel brought all of "Wag More Dog Store" detail records, and that total along, it didn't sort within these records, it just brought them in their original sequence, it took, I always say, it's an entire chunk of data.
And then bradedgar.com came up, as the second group, and then CPASelfStudy as the third group.
I was blown away, I couldn't believe that Microsoft actually did something like this though, did the right thing, in order to get things to sort.
And let's think about if we would sort by customer, right, the grand total is going to come up somewhere into the middle of data, right before "Hartville Marketplace & Flea Market", but if we click "A to Z", they're smart enough to keep the grand total at the bottom, and it doesn't swing up in between "F Keys" and "Hartville Marketplace".
Absolutely amazing, amazing trick!
And it's just one of the 40 tricks in the book, go ahead click that "i" in the top right-hand corner.
Cheap to buy the book, 25$ in print, 10$ e-book, Great reference to all of these podcasts.
Again thanks to my friend Derek Fraley in Springfield, MO for this trick, and several others that thege give me along the way.
First you add the subtotals, use the Group and Outline button to show only the subtotals, sort descending by revenue, and Excel sorts chunks of records, or you can sort again by customer, and the grand total goes back to the bottom.
Great, great trick.
I want to thank you for stopping by, we'll see you next time for another NetCast from MrExcel!
Go ahead and subscribe to the playlist, I'll be podcasting this entire book.
Welcome back to MrExcel netcast, I'm Bill Jelen.
Today we're talking about subtotals, a great feature that's been around since Excel 97!
I have data here, I make sure to sort the data by customer, hundreds of rows of data, lots of customers.
When I go into Data, and then Subtotal, Add each change in: , they always want to subtotal by the leftmost column, you can change that in: Customer.
They offer 11 functions here - sum, count, average, as long as your rightmost column is numeric, we are going to choose the three columns here, or if you want every customer on their own page, page break between groups, click OK.
And then what we get is, every time the customer changes, we get a new subtotal row that was added.
At the very bottom we even get a grand total.
Alright, that grand total is hard to do, but, thanks to the subtotal function.
But over here on the left-hand side we have three new buttons: grouped outline buttons, that #2 view.
That is the beautiful view of the data.
Alright so, I was in Springfield, MO, and someone asked me is there a way to sort the subtotals, so that the largest customer is at the top.
Alright, and I started to think about this, and it's like, each of these customers has several records associated with it, If I would somehow sort this and get the largest value to come to the top, well, you know it would be a formula pointing at rows above us, and it's not there anymore, but Derek was back there on row 6, Derek says "Hey, no, wait, you can do this, just choose one cell in the revenue column, and click ZA." I'm like "Derek, I don't think that'll work, there's no way that'll work." Derek says "Trust me, just try it." Alright so here I'm going to sort this, and "Wag More Dog Store, San Antonio" comes to the top, but they don't come to row 2, it comes to row 49.
And if we go look at the #3 view, which shows us all the detail, you see that Excel brought all of "Wag More Dog Store" detail records, and that total along, it didn't sort within these records, it just brought them in their original sequence, it took, I always say, it's an entire chunk of data.
And then bradedgar.com came up, as the second group, and then CPASelfStudy as the third group.
I was blown away, I couldn't believe that Microsoft actually did something like this though, did the right thing, in order to get things to sort.
And let's think about if we would sort by customer, right, the grand total is going to come up somewhere into the middle of data, right before "Hartville Marketplace & Flea Market", but if we click "A to Z", they're smart enough to keep the grand total at the bottom, and it doesn't swing up in between "F Keys" and "Hartville Marketplace".
Absolutely amazing, amazing trick!
And it's just one of the 40 tricks in the book, go ahead click that "i" in the top right-hand corner.
Cheap to buy the book, 25$ in print, 10$ e-book, Great reference to all of these podcasts.
Again thanks to my friend Derek Fraley in Springfield, MO for this trick, and several others that thege give me along the way.
First you add the subtotals, use the Group and Outline button to show only the subtotals, sort descending by revenue, and Excel sorts chunks of records, or you can sort again by customer, and the grand total goes back to the bottom.
Great, great trick.
I want to thank you for stopping by, we'll see you next time for another NetCast from MrExcel!
