The Subtotal command in Excel 2010 allows you to quickly add subtotals to a Data Set. In Episode #1325, Bill shows us how to collapse the results to show only the subtotals and then how to bring the largest subtotals to the top of the results.
Transcript of the video:
MrExcel podcast is sponsored by Excel-XL.
Wrap up to Excel 2010, chapter 18; Subtotals.
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
We're working through the Rev up to Excel 2010, although, this really is a new to Excel 2010, subtotals have been around for a long long time, a huge huge time saver, at least going back to Excel 97.
So, I have a data set here and I want to add totals by product, they're easy to do, using the subtotals command.
First thing you wanna do, is you wanna make sure that you sort this data whatever you're subtitling, so I sort by the product field, puts all of those products together and we want to come to the data tab, out to the Outline group and it's gonna go, off the screen, that's just a noise made through out.
Here, where you can't see it, sorry, so I put it up here on the quick access toolbar.
All right, the subtotal dialog box, a few tips here.
It always wants the subtotal by the left most column, and that's hardly ever, what you want to do?
is to make sure to check that, it looks at the right most column, if the right most column is numeric, then it chooses Sum here, otherwise you might get a Count, if you get a Count, you probably wanna change it.
Now there's 11 different options there.
some is the right one, in this case.
so we're good and then we choose the numeric fields, if you wanna every customer on their own page, Page break between groups, click OK and Bam! Just that fast, it went through and every time that the product changed, it inserted a brand new row, the product name, the word total and then the totals for that product.
An amazing, amazing feature that really could save you a lot of time, if you're doing the totals, manually.
Now, at your left-hand side, three new buttons, 1, 2, and 3 and press the number 2 button.
[ I'll ] collapse everything down and show just one line, co-product.
The other thing, that's really amazing to me and what a lot of people don't know about subtotals is it, if we wanna see the largest product at the top, when it's collapsed, this number 2 view can come over here, to column F and we'll click Z to A, the revenue column and it brought the largest product to the top, I wanna go back to the number 3 view, you'll see, that it actually brought all of the records along with that total.
All right so it didn't just bring the total along, it brought, it actually sorted an entire chunk of records.
In this case, 109 records came to the top.
And then the next job, it doesn't sort within the group, but it just brings all of those records up to the top, so, fairly cool feature, automatic subtotals path around the data menu, for a long long time, in the right situation, save you an immense amount of time.
Hey, I wanna thank you for stopping by. See you next time for another netcast from MrExcel.
Wrap up to Excel 2010, chapter 18; Subtotals.
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
We're working through the Rev up to Excel 2010, although, this really is a new to Excel 2010, subtotals have been around for a long long time, a huge huge time saver, at least going back to Excel 97.
So, I have a data set here and I want to add totals by product, they're easy to do, using the subtotals command.
First thing you wanna do, is you wanna make sure that you sort this data whatever you're subtitling, so I sort by the product field, puts all of those products together and we want to come to the data tab, out to the Outline group and it's gonna go, off the screen, that's just a noise made through out.
Here, where you can't see it, sorry, so I put it up here on the quick access toolbar.
All right, the subtotal dialog box, a few tips here.
It always wants the subtotal by the left most column, and that's hardly ever, what you want to do?
is to make sure to check that, it looks at the right most column, if the right most column is numeric, then it chooses Sum here, otherwise you might get a Count, if you get a Count, you probably wanna change it.
Now there's 11 different options there.
some is the right one, in this case.
so we're good and then we choose the numeric fields, if you wanna every customer on their own page, Page break between groups, click OK and Bam! Just that fast, it went through and every time that the product changed, it inserted a brand new row, the product name, the word total and then the totals for that product.
An amazing, amazing feature that really could save you a lot of time, if you're doing the totals, manually.
Now, at your left-hand side, three new buttons, 1, 2, and 3 and press the number 2 button.
[ I'll ] collapse everything down and show just one line, co-product.
The other thing, that's really amazing to me and what a lot of people don't know about subtotals is it, if we wanna see the largest product at the top, when it's collapsed, this number 2 view can come over here, to column F and we'll click Z to A, the revenue column and it brought the largest product to the top, I wanna go back to the number 3 view, you'll see, that it actually brought all of the records along with that total.
All right so it didn't just bring the total along, it brought, it actually sorted an entire chunk of records.
In this case, 109 records came to the top.
And then the next job, it doesn't sort within the group, but it just brings all of those records up to the top, so, fairly cool feature, automatic subtotals path around the data menu, for a long long time, in the right situation, save you an immense amount of time.
Hey, I wanna thank you for stopping by. See you next time for another netcast from MrExcel.
