Accountants love the subtotals feature in Excel. We love to see details but add a little summary information. I have a lot of friends who think Subtotals should be deprecated and that anytime you use subtotals, you should be using a pivot table instead. That's rubbish. There are times when subtotals are the perfect solution. What do you think? Do you love them or loathe them?
Transcript of the video:
So I have an invoice Register here today and my manager wants me to add just a little bit of summary to this.
I'm going to do that in nine clicks.
First thing the data is sorted by date right now.
I want to sort by customer.
So: Data, A-Z to sort ascending.
I'm still on the Data tab.
Come over here Subtotal.
At each change in: Customer.
Use the Sum function.
They already checked the rightmost column.
I'm going to check three more columns, click OK, and there we go.
Now I have a little subtotal after each of the customers.
The important thing here is I need to see the details right. I am not trying to summarize the whole thing, I need this detailed information, but I want to have the ability to see some subtotals.
And the subtotal function out there, ever since Excel 97 has given us that ability.
Now there's extra things you can get with subtotals.
Over here, on the left hand side are the Group and Outline buttons.
They were added by the Subtotal command.
I love that number 2 button that gives me a nice little summary in case I need that summary.
You can even - this shocks people - when it's collapsed down to this number two view you can sort descending.
And Walmart doesn't come to row two.
It comes to row 67.
And what just happened is all of the Walmart records (in their original sequence, it didn't sort within that group) - that whole group got sorted to the top.
That's wild if I need to copy this somewhere, it's not that hard.
Select the data.
One extra step, alt semi colon.
That's the shortcut for Home, Find & Select, Go To Special, Visible Cells Only, OK.
And then I can copy; Ctrl+N for New, Ctrl+V to Paste,.
easy enough.
Here's one where I already subtotal by Sector, and I can add a second level of subtotals.
So here: Subtotal, At each change in Customer.
Just uncheck Replace Current Subtotals.
And now I have this awesome little view where I have two levels of subtotals.
The two button shows me just sectors.
The three button customer.
For those of you who say they're ugly.
Again, alt+semi colon.
We will do a little bit of formatting here on the customers.
And then go to the number two view.
Select the Sectors with Alt semicolon again.
Some different formatting.
And now as I Scroll down through, I've nicely formatted the customer subtotal rows and the sector subtotal rows.
[ Lauging ] That was all a preamble.
Let's get started.
Learn Excel from MrExcel Podcast episode 2428.
In defense of Subtotals.
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, I am curious about this.
There’s a lot of my friends who hate subtotals.
I've collected some quotes that I've heard over the last six months.
“Second only to Merge Cells feature as Bad Excel features”.
“Wouldn't cry if they were deprecated”.
“They are rubbish”.
“The only people I know who use them are accountants, especially those not prepared to accept pivot tables”.
I can see that this is half right.
I mean, I worked in accounting for ten years.
I used subtotals every day of my life.
But this though… that it's someone who's not prepared to accept pivot tables.
That's not true.
I love pivot tables.
I mean, I probably create 10 times more pivot tables than subtotals.
But there's times when a subtotal is the right thing.
I mean, let's face it, I've sold 50,000 books teaching people how to use pivot tables.
I'm not someone who's “Not prepared to accept pivot tables”.
Alright, so I took this to Twitter.
I love them.
A lot of people seem to loathe them.
Where do you stand?
And it was a wide variety of opinions.
A lot of people over here on the love side.
David Ringstrom complained that you have to do the alt semi colon thing, whereas filters you wouldn't have to do that.
Uses it when teaching Excel.
And boy I agree with that.
The people who really in my classes love subtotals are the people who currently are doing this.
[ Silence while Bill manually adds 27 subtotal rows ].
Geez, right?
For someone who's doing that every day of their lives?
When you show them subtotals?
Oh my God, it's life changing.
But over here on the loathe side.
A lot of people seem to think, well, you should be using a pivot table.
You shouldn't be using subtotals.
This was actually a great conversation, “but if it can remotely be a pivot table, it will be”.
And someone said, “Trust me, it can be a pivot table”.
Well, hang on.
I mean, just because it CAN be a pivot table doesn't mean that it should.
Let me try and recreate the subtotal look in pivot table and remember we need to see all the details.
Insert pivot table.
OK.
Choose customer first.
You have to choose customer first, which is going to hack off my manager.
He doesn't want customers to be first.
He wants customer to be where it is.
So we choose all of these items.
And we get all of these extra subtotals that I don't want.
I just want one total per customer.
To turn all of those off on the Design tab, Subtotals, Do not show subtotals.
And then I have to come back into Customer.
PivotTable Analyze.
Field settings.
And then turn them back on for that one.
Now so we have customer and over here the customer totals.
But even when I collapse this.
You know, like the equivalent of clicking the number two view.
You know, my customer is now a whole bunch of columns from the data.
And I can't put customer here in column G.
Because it's just not right.
It's not going to work.
So this is a case where, yeah, I could get that data from a pivot table.
But in this particular case, needing to see all of the details like I do, a simple set of Subtotals is the way to go.
You know the thing that bothers me here is this word “deprecated”.
That's Microsoft-speak for, “Let's rip this feature out of Excel”.
And I get it.
Alright, you don't like them, you don't use them.
But there's a lot of us accountants (and a lot of other people, I bet), who use subtotals all the time.
It is a great way to show a lot of detailed data with a simple summary.
You probably have features that you use all the time and I don't use them.
I don't call for those features to be removed!
There you are.
So down below, let me know which side are you on.
Are you on the “Hate subtotals” or “I love subtotals”.
And by the way, we're not talking about the =SUBTOTAL function.
SUBTOTAL and AGGREGATE: great uses for that.
I'm just talking about this feature out here on the Data tab.
All of these subtotal tricks are in my book, MrExcel 2021, Unmasking Excel.
If you like these videos, please, down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank my Excel MVP friends for their opinions that subtotals are horrible and I want to thank everyone on Twitter who came to their defense.
I thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm going to do that in nine clicks.
First thing the data is sorted by date right now.
I want to sort by customer.
So: Data, A-Z to sort ascending.
I'm still on the Data tab.
Come over here Subtotal.
At each change in: Customer.
Use the Sum function.
They already checked the rightmost column.
I'm going to check three more columns, click OK, and there we go.
Now I have a little subtotal after each of the customers.
The important thing here is I need to see the details right. I am not trying to summarize the whole thing, I need this detailed information, but I want to have the ability to see some subtotals.
And the subtotal function out there, ever since Excel 97 has given us that ability.
Now there's extra things you can get with subtotals.
Over here, on the left hand side are the Group and Outline buttons.
They were added by the Subtotal command.
I love that number 2 button that gives me a nice little summary in case I need that summary.
You can even - this shocks people - when it's collapsed down to this number two view you can sort descending.
And Walmart doesn't come to row two.
It comes to row 67.
And what just happened is all of the Walmart records (in their original sequence, it didn't sort within that group) - that whole group got sorted to the top.
That's wild if I need to copy this somewhere, it's not that hard.
Select the data.
One extra step, alt semi colon.
That's the shortcut for Home, Find & Select, Go To Special, Visible Cells Only, OK.
And then I can copy; Ctrl+N for New, Ctrl+V to Paste,.
easy enough.
Here's one where I already subtotal by Sector, and I can add a second level of subtotals.
So here: Subtotal, At each change in Customer.
Just uncheck Replace Current Subtotals.
And now I have this awesome little view where I have two levels of subtotals.
The two button shows me just sectors.
The three button customer.
For those of you who say they're ugly.
Again, alt+semi colon.
We will do a little bit of formatting here on the customers.
And then go to the number two view.
Select the Sectors with Alt semicolon again.
Some different formatting.
And now as I Scroll down through, I've nicely formatted the customer subtotal rows and the sector subtotal rows.
[ Lauging ] That was all a preamble.
Let's get started.
Learn Excel from MrExcel Podcast episode 2428.
In defense of Subtotals.
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, I am curious about this.
There’s a lot of my friends who hate subtotals.
I've collected some quotes that I've heard over the last six months.
“Second only to Merge Cells feature as Bad Excel features”.
“Wouldn't cry if they were deprecated”.
“They are rubbish”.
“The only people I know who use them are accountants, especially those not prepared to accept pivot tables”.
I can see that this is half right.
I mean, I worked in accounting for ten years.
I used subtotals every day of my life.
But this though… that it's someone who's not prepared to accept pivot tables.
That's not true.
I love pivot tables.
I mean, I probably create 10 times more pivot tables than subtotals.
But there's times when a subtotal is the right thing.
I mean, let's face it, I've sold 50,000 books teaching people how to use pivot tables.
I'm not someone who's “Not prepared to accept pivot tables”.
Alright, so I took this to Twitter.
I love them.
A lot of people seem to loathe them.
Where do you stand?
And it was a wide variety of opinions.
A lot of people over here on the love side.
David Ringstrom complained that you have to do the alt semi colon thing, whereas filters you wouldn't have to do that.
Uses it when teaching Excel.
And boy I agree with that.
The people who really in my classes love subtotals are the people who currently are doing this.
[ Silence while Bill manually adds 27 subtotal rows ].
Geez, right?
For someone who's doing that every day of their lives?
When you show them subtotals?
Oh my God, it's life changing.
But over here on the loathe side.
A lot of people seem to think, well, you should be using a pivot table.
You shouldn't be using subtotals.
This was actually a great conversation, “but if it can remotely be a pivot table, it will be”.
And someone said, “Trust me, it can be a pivot table”.
Well, hang on.
I mean, just because it CAN be a pivot table doesn't mean that it should.
Let me try and recreate the subtotal look in pivot table and remember we need to see all the details.
Insert pivot table.
OK.
Choose customer first.
You have to choose customer first, which is going to hack off my manager.
He doesn't want customers to be first.
He wants customer to be where it is.
So we choose all of these items.
And we get all of these extra subtotals that I don't want.
I just want one total per customer.
To turn all of those off on the Design tab, Subtotals, Do not show subtotals.
And then I have to come back into Customer.
PivotTable Analyze.
Field settings.
And then turn them back on for that one.
Now so we have customer and over here the customer totals.
But even when I collapse this.
You know, like the equivalent of clicking the number two view.
You know, my customer is now a whole bunch of columns from the data.
And I can't put customer here in column G.
Because it's just not right.
It's not going to work.
So this is a case where, yeah, I could get that data from a pivot table.
But in this particular case, needing to see all of the details like I do, a simple set of Subtotals is the way to go.
You know the thing that bothers me here is this word “deprecated”.
That's Microsoft-speak for, “Let's rip this feature out of Excel”.
And I get it.
Alright, you don't like them, you don't use them.
But there's a lot of us accountants (and a lot of other people, I bet), who use subtotals all the time.
It is a great way to show a lot of detailed data with a simple summary.
You probably have features that you use all the time and I don't use them.
I don't call for those features to be removed!
There you are.
So down below, let me know which side are you on.
Are you on the “Hate subtotals” or “I love subtotals”.
And by the way, we're not talking about the =SUBTOTAL function.
SUBTOTAL and AGGREGATE: great uses for that.
I'm just talking about this feature out here on the Data tab.
All of these subtotal tricks are in my book, MrExcel 2021, Unmasking Excel.
If you like these videos, please, down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank my Excel MVP friends for their opinions that subtotals are horrible and I want to thank everyone on Twitter who came to their defense.
I thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
