The subtotal command is a fantastic time-saver, but it occasionally behaves erratically. In Episode 861, I will show two situations which can cause the subtotal command to whack out.
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
A couple of subtotal issues that we finally solved, one of these with the help of Jeremy from Grand Rapids.
Occasionally, when I do my seminars, people will talk about weird things that have happened with subtotals, and, you know, we can never replicate it right there.
We finally figured one out.
If you add subtotals to this data set-- so let me, I guess, sort by Customer first, and then we'll choose all Sales data subtotals, and then say "At each change in:" Customer, we'll add the subtotals to Profit, Cost of Goods Sold, Revenue, click OK.
Now, people say, "Wait a second, for some reason, the Walmart total and the Grand Total are showing up all the way down here at the bottom, after all of these blank rows.
We can't figure out why that's happening.
you know, what's going on?
Why did Excel put all those blank rows in?" So let me undo.
Turns out that this happens anytime that you have some activated rows to the bottom-- so if you do End, Home, and Excel thinks that there's data down there in 676-- and second thing, you don't just select one cell, you select the entire spreadsheet-- so select the entire spreadsheet before you create subtotals.
Now, my solution to this is, it's not necessary to choose the entire spreadsheet, provided we have no blank rows and no blank columns; you can simply just choose one cell in the data, and when you-- Data, Subtotals-- Excel will automatically extend that out to the edge of the data and, more importantly, at the bottom, it won't include those extra rows that Excel thinks are part of the spreadsheet but are really all blank.
So by selecting one cell, you'll solve that problem.
Now, here's another one.
I ran into a spreadsheet that someone sent me, and this person doesn't know Excel that well.
And at the bottom of their data set, they had done two things.
The first thing they had was a formula here-- an =SUM formula that summed everything up-- and then, for whatever reason, they typed the number again-- probably just to see if, you know, as a check number to see if anything changed or something like that.
So I got this data set, I clicked A to Z to sort by Customer-- Data, Subtotals, "At each change in:" Customer, we'll sum Sales-- I collapsed that down to the #2 view and then used the trick where we can sort Z to A in order to bring the largest Customers to the top.
But, strangely, what happened there is because of that extra sum that had been below the data.
That extra sum got copied in and basically caused the data set to get doubled.
If I come down here to my Grand Total, you'll see that instead of being 26 million, it's 53 million.
So, a couple of things.
First of all, watch out for the blank rows that are masquerading as real rows beneath your data set.
You want to use just a single cell in your data, to prevent that.
And then, again, watch out for any totals that are already below your data-- particularly, this weird situation where we had a sum, and then a hard-coded number beneath that data, certainly can cause havoc with the subtotal.
If you're careful, Subtotal is a great command; works great.
I've used it thousands of times.
However, when you have some strange data, you might run into a few problems here and there.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
A couple of subtotal issues that we finally solved, one of these with the help of Jeremy from Grand Rapids.
Occasionally, when I do my seminars, people will talk about weird things that have happened with subtotals, and, you know, we can never replicate it right there.
We finally figured one out.
If you add subtotals to this data set-- so let me, I guess, sort by Customer first, and then we'll choose all Sales data subtotals, and then say "At each change in:" Customer, we'll add the subtotals to Profit, Cost of Goods Sold, Revenue, click OK.
Now, people say, "Wait a second, for some reason, the Walmart total and the Grand Total are showing up all the way down here at the bottom, after all of these blank rows.
We can't figure out why that's happening.
you know, what's going on?
Why did Excel put all those blank rows in?" So let me undo.
Turns out that this happens anytime that you have some activated rows to the bottom-- so if you do End, Home, and Excel thinks that there's data down there in 676-- and second thing, you don't just select one cell, you select the entire spreadsheet-- so select the entire spreadsheet before you create subtotals.
Now, my solution to this is, it's not necessary to choose the entire spreadsheet, provided we have no blank rows and no blank columns; you can simply just choose one cell in the data, and when you-- Data, Subtotals-- Excel will automatically extend that out to the edge of the data and, more importantly, at the bottom, it won't include those extra rows that Excel thinks are part of the spreadsheet but are really all blank.
So by selecting one cell, you'll solve that problem.
Now, here's another one.
I ran into a spreadsheet that someone sent me, and this person doesn't know Excel that well.
And at the bottom of their data set, they had done two things.
The first thing they had was a formula here-- an =SUM formula that summed everything up-- and then, for whatever reason, they typed the number again-- probably just to see if, you know, as a check number to see if anything changed or something like that.
So I got this data set, I clicked A to Z to sort by Customer-- Data, Subtotals, "At each change in:" Customer, we'll sum Sales-- I collapsed that down to the #2 view and then used the trick where we can sort Z to A in order to bring the largest Customers to the top.
But, strangely, what happened there is because of that extra sum that had been below the data.
That extra sum got copied in and basically caused the data set to get doubled.
If I come down here to my Grand Total, you'll see that instead of being 26 million, it's 53 million.
So, a couple of things.
First of all, watch out for the blank rows that are masquerading as real rows beneath your data set.
You want to use just a single cell in your data, to prevent that.
And then, again, watch out for any totals that are already below your data-- particularly, this weird situation where we had a sum, and then a hard-coded number beneath that data, certainly can cause havoc with the subtotal.
If you're careful, Subtotal is a great command; works great.
I've used it thousands of times.
However, when you have some strange data, you might run into a few problems here and there.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
