Geert asks: "in my experience the datasets are non-uniform in the sense that only a subpart of the data needs subtotals. (categories of data are sequentially listed and only 1 category has a further breakdown that needs subtotals – the other categories do/must not) Is it possible to do that using the buttons on the ribbon?
This episode shows how to add subtotals to each customer in the manufacturing region.
This episode shows how to add subtotals to each customer in the manufacturing region.
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2432.
Is it possible to add subtotals to only one category of your data? Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today's question after episode 2428 about Subtotals.
Someone said well, look I have data where I have several categories that don't need subtotals.
B ut then there's one category that has a needs a further breakdown and just that category needs subtotals, not the other categories.
And I thought about that.
And I said, alright, so usually we have headings above our data.
And the subtotals in the dialog box it gives those heading names.
But if you look at the VBA that's recorded, they actually don't care what the column heading names are.
So here like, I have communications has only one customer.
Energy, only one customer. Financial one customer.
But manufacturing is the one that need subtotals because there's a lot of different customers in manufacturing.
So this is my data set similar to what the person described.
I'm going to choose the last data row in healthcare and those are going to be my defacto headings for manufacturing.
I'll cruise down through until the last manufacturing row.
Then, Data, Subtotal. And this is going to look funny.
Well, first off they say, hey, it doesn't seem like those are headings.
You are right, they're not, but treat him like headings.
And then at each change in (customer) Merck, use the sum function and then choose these 4 columns here. Click OK.
Now the only hassle we get is we get a grand total.
And I had to go back.
There is a video #849 that I did so long ago that shows how to remove the grand total from the subtotals.
You start here at the grand total row and you go up to the very first data row. So just below Merck right?
And then click ungroup and that removes that extra buttons.
Now we just have two buttons one and two like that.
And then you can just right click and delete the grand total row.
And we're good to go. Now I start to think about this.
I thought, aren't you really going to want subtotals by retail and transportation?
The categories that only have one. He didn't say that he did.
So this is a bonus. Let's let's just try it.
So what we are going to do - this time from sector.
Subtotals.
Very important to uncheck Replace Current Subtotals.
That way we don't lose the subtotals we just added.
At each change in Sector use the sum function.
These are going to be off by one because it remembers it was column 2, 3, 4, 5.
You know that's that's really what's getting saved here.
These these heading names aren't important at all in the big scheme of things, right? So now we have a look like this.
So in the number 2 button we have totals for the things that didn't need further subtotals.
We get their totals and then for the items that needed the subtotals they show up like that.
That wasn't part of the request, but it might be useful.
Who knows?
Alright, check out my book MrExcel 2021 Unmasking Excel.
Click the I in the top right hand corner for more information about that.
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 you for stopping by.
I will see you next time for another netcast from MrExcel.
Is it possible to add subtotals to only one category of your data? Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today's question after episode 2428 about Subtotals.
Someone said well, look I have data where I have several categories that don't need subtotals.
B ut then there's one category that has a needs a further breakdown and just that category needs subtotals, not the other categories.
And I thought about that.
And I said, alright, so usually we have headings above our data.
And the subtotals in the dialog box it gives those heading names.
But if you look at the VBA that's recorded, they actually don't care what the column heading names are.
So here like, I have communications has only one customer.
Energy, only one customer. Financial one customer.
But manufacturing is the one that need subtotals because there's a lot of different customers in manufacturing.
So this is my data set similar to what the person described.
I'm going to choose the last data row in healthcare and those are going to be my defacto headings for manufacturing.
I'll cruise down through until the last manufacturing row.
Then, Data, Subtotal. And this is going to look funny.
Well, first off they say, hey, it doesn't seem like those are headings.
You are right, they're not, but treat him like headings.
And then at each change in (customer) Merck, use the sum function and then choose these 4 columns here. Click OK.
Now the only hassle we get is we get a grand total.
And I had to go back.
There is a video #849 that I did so long ago that shows how to remove the grand total from the subtotals.
You start here at the grand total row and you go up to the very first data row. So just below Merck right?
And then click ungroup and that removes that extra buttons.
Now we just have two buttons one and two like that.
And then you can just right click and delete the grand total row.
And we're good to go. Now I start to think about this.
I thought, aren't you really going to want subtotals by retail and transportation?
The categories that only have one. He didn't say that he did.
So this is a bonus. Let's let's just try it.
So what we are going to do - this time from sector.
Subtotals.
Very important to uncheck Replace Current Subtotals.
That way we don't lose the subtotals we just added.
At each change in Sector use the sum function.
These are going to be off by one because it remembers it was column 2, 3, 4, 5.
You know that's that's really what's getting saved here.
These these heading names aren't important at all in the big scheme of things, right? So now we have a look like this.
So in the number 2 button we have totals for the things that didn't need further subtotals.
We get their totals and then for the items that needed the subtotals they show up like that.
That wasn't part of the request, but it might be useful.
Who knows?
Alright, check out my book MrExcel 2021 Unmasking Excel.
Click the I in the top right hand corner for more information about that.
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 you for stopping by.
I will see you next time for another netcast from MrExcel.
