Mike from ExcelIsFun throws down this week's challenge: adding horizontal subtotals. Bill and Mike duel it out in Episode 990.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, I'm Bill Jelen from MrExcel.Com.
I've got a cool Excel tip for you today.
Hey, this is Mike Gel Girvin.
I am Excel Is Fun on YouTube.
And I have a different way to do that.
Hey, alright! Welcome back.
This is Bill Jelen.
We have another dueling Excel podcast.
Mike came up with this week's problem.
And why this is a tough one, Michael wants to do horizontal subtotals.
Horizontal..
Now, if the data was oriented vertically, would be easy data subtotal of each change in month Add the subtotal the sales.
And we'd be done, but you can't do that with horizontal.
I hope that Mike has a cool tip in mind for this one because to be honest I'm g be going to be struggling.
I select that whole data set.
Go into the sort dialog.
First thing options.
Say that we're going to sort left to right instead of top to bottom.
Very unusual.
We're going to sort on row 1 and instead of sorting A to Z, I'm going to go into Custom lists and choose the month abbreviations.
Click OK.
Click OK.
and that brings all the January together and then the February.
Now, I'm going to add a brand new calculation here.
It's called change and I'm going to put a little formula.
And equal if this Column is equal to the previous column then I put a 1 otherwise.
I put the letter A.
Does not matter what those are but it is important that one of them is numeric, and the other one is not numeric.
Will copy that across.
I want to change those formulas to values .
So, CTRL+C ALT+E+S+V+Enter and we're good to go there.
Now, I have that area selected.
I'm going to go to go to specials.
So, that's F5 and then special.
We can also get to that under home, find and select go to special and I want to choose the constants that are text.
That will get me the first month selected.
And now, that we have that We'll insert column L.
I see and bam!
Just like that we get blank columns.
Draw it in throughout the data set.
At that point, I can now get rid of row 4 and we're going to use a cool trick.
Here, I'm going to start in that first blank cell and go all the way over to the right.
Again, I'm going to use go to special.
So, F5 Special...
Select only the blanks and now that I've selected just those blank cells, the auto sum button, ALT equals is very smart.
It will very quickly go through and add in the correct subtotals.
Format column auto fit.
We're good to go.
To get the month totals up there, start from the first blank over to the last blank.
Again, F5 special>Blanks add equal, [ left arrow+CTRL+Enter ] It puts our totals in.
Maybe I'll put those [ CTRL+B ] to put them in Bold And there you have it, Horizontal subtotals.
Wow! Again, I'm going to throw this over to Mike.
I hope he has something much cooler in mind that what I had to go through.
Mike.
Thanks, MrExcel!
Wow!I hope you guys had your pencil and paper out.
There were so many great tricks in that.
Go to blank, sort horizontally and Sort custom.
So you get January, February.
Now what MrExcel did.
He actually simulated exactly what subtotals, vertically does.
He inserted a whole column here like the subtotals.
Insert rows with a label here, a blank and then whatever the subtotals.
I do not know how to do that So I'm going to have to do it slightly a different way.
I'm going to click on this sheet right here.
Now, I got to show you how easy it is to do subtotals vertically.
This question about horizontal subtotals, came from someone at YouTube.
And they just got to tell their boss.
Basically, here's how to do it vertically.
And it's much easier.
So, let's do it this way.
To subtotal, you need to sort the field you want to add subtotals to.
So, I've already sorted this.
And the subtotal in 2007, I go to the data ribbon and subtotal.
In earlier versions, you click in one cell and you...
The data menu subtotals, which is [ ALT+D+B ] And then it's very simple because I've already sorted.
I say at each change in months sums function subtotals at sales because that's what I want to add up.
And then click OK.
Just like that.
Boom!
Subtotals for January, Subtotals for February.
Now, here's what I did.
I didn't insert columns, but I added a totals row.
And I actually want to do a formula here.
That will put a blank in the cell if we're still at January.
But do a subtotal if we get to the last month for this period.
So here's the formula I did, equals if and a logical test is going to be one, two, three cells above.
Is this label not and not is less than greater than is that not equal to whatever the next label is.
That way when we get take this formula brrr over to hear the H column.
It's looking at label January, label Februari.
They are not equal So, that's where we want our subtotal.
Comma so, that that's the logical test.
The value is true which means these are not equal.
It's going to just be SUMIF.
So, [ =SUMIF ] And there's a range and a criteria.
The criteria goes with the range and then of course the screen tip says, sum_range.
Those are the things we want to add.
So, the range we're going to evaluate.
I'm going to click in this cell.
[ CTRL+SHIFT+right arrow ] then F4 to lock it and jump the screen back in view.
comma and the criteria is going to be one, two, three cells above because when we get over to this column right, we want to subtotal it.
All it needs to do is look at that label comma and then the sum range is this.
[ CTRL+SHIFT+right arrow and F4 ] Close parentheses and notice the screen tip tells us oh.
Oh, that's our value for true for the if formula.
So, we type a comma and the value if false is going to course be Double-quote, double-quote for blank.
Close [ parenthesis ] [ CTRL+ENTER ] Now, I'm going to drag this over and it looks like it got our totals.
Now, one last thing.
Since I couldn't insert a column and do all that cool stuff, maybe I'll do some conditional formatting.
So, we get a color here and a color here, to visually indicate that these are the subtotals.
I'm going to highlight this whole row here.
Use the scroll arrow to come back and watch this.
I'm going to hold CTRL and click right here.
Drag all the way to the end.
Actually, let me do that a much cooler way.
Watch this [ CTRL+SHIFT+right-arrow ] and then I'm going to scroll back.
Holding CTRL, I'm going to click here.
And then after I've clicked there, I'm going to [ CTRL+SHIFT+right-arrow ] So, we can do that selection trick on non contiguous ranges.
We want to do conditional formatting.
The active cell is right there.
So we're going to do a true/false formula.
Now in 2007, you go to Home>Conditional Formatting >New Rules.
In earlier versions to go to the Format Menu, I'm going to use the keyboard shortcut [ ALT+O+D ], [ ALT+O+D ]...
In this version, you click on New Rule.
And then formula down here and click in this text box in earlier versions.
You click the first drop down and point to formula is.
Now, what is our true false formula.
We are going to be for all of these cells.
Where we're always going to ask is this.
But notice there's two dollar signs hit the F4 key because we want dollar sign just in front of the row.
So, when the formula in memory, the true false from that gets copied down here to this cell.
It's still looking at that.
January not ...
The next one and we need to lock that also.
In front of the row that's the true false formula.
I'm going to click Format.
I'm going to click fill.
That used to say patterns>red Font, I'm going to click on this white.
Because the value of the color red.
This doesn't you don't think of this as a dark color.
But the value of it is dark, and so I'm going to use...
Oops! I didn't get the right format.
The value is quite dark.
So, I'm going to select a light colored font.
Click OK.
I'll click OK and sure enough.
Now we have our conditional formatting.
Okay! so that's a horizontal Subtotals.
Hey, alright! mike those were great.
I love that idea of just having the subtitles down in the bottom row and also your comment.
If your manager can figure out how this works, he can do or settle Subtotals.
Otherwise go with vertical.
Of course this is so much easier to do vertical.
Alright! I want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel and Excel Is Fun.
I've got a cool Excel tip for you today.
Hey, this is Mike Gel Girvin.
I am Excel Is Fun on YouTube.
And I have a different way to do that.
Hey, alright! Welcome back.
This is Bill Jelen.
We have another dueling Excel podcast.
Mike came up with this week's problem.
And why this is a tough one, Michael wants to do horizontal subtotals.
Horizontal..
Now, if the data was oriented vertically, would be easy data subtotal of each change in month Add the subtotal the sales.
And we'd be done, but you can't do that with horizontal.
I hope that Mike has a cool tip in mind for this one because to be honest I'm g be going to be struggling.
I select that whole data set.
Go into the sort dialog.
First thing options.
Say that we're going to sort left to right instead of top to bottom.
Very unusual.
We're going to sort on row 1 and instead of sorting A to Z, I'm going to go into Custom lists and choose the month abbreviations.
Click OK.
Click OK.
and that brings all the January together and then the February.
Now, I'm going to add a brand new calculation here.
It's called change and I'm going to put a little formula.
And equal if this Column is equal to the previous column then I put a 1 otherwise.
I put the letter A.
Does not matter what those are but it is important that one of them is numeric, and the other one is not numeric.
Will copy that across.
I want to change those formulas to values .
So, CTRL+C ALT+E+S+V+Enter and we're good to go there.
Now, I have that area selected.
I'm going to go to go to specials.
So, that's F5 and then special.
We can also get to that under home, find and select go to special and I want to choose the constants that are text.
That will get me the first month selected.
And now, that we have that We'll insert column L.
I see and bam!
Just like that we get blank columns.
Draw it in throughout the data set.
At that point, I can now get rid of row 4 and we're going to use a cool trick.
Here, I'm going to start in that first blank cell and go all the way over to the right.
Again, I'm going to use go to special.
So, F5 Special...
Select only the blanks and now that I've selected just those blank cells, the auto sum button, ALT equals is very smart.
It will very quickly go through and add in the correct subtotals.
Format column auto fit.
We're good to go.
To get the month totals up there, start from the first blank over to the last blank.
Again, F5 special>Blanks add equal, [ left arrow+CTRL+Enter ] It puts our totals in.
Maybe I'll put those [ CTRL+B ] to put them in Bold And there you have it, Horizontal subtotals.
Wow! Again, I'm going to throw this over to Mike.
I hope he has something much cooler in mind that what I had to go through.
Mike.
Thanks, MrExcel!
Wow!I hope you guys had your pencil and paper out.
There were so many great tricks in that.
Go to blank, sort horizontally and Sort custom.
So you get January, February.
Now what MrExcel did.
He actually simulated exactly what subtotals, vertically does.
He inserted a whole column here like the subtotals.
Insert rows with a label here, a blank and then whatever the subtotals.
I do not know how to do that So I'm going to have to do it slightly a different way.
I'm going to click on this sheet right here.
Now, I got to show you how easy it is to do subtotals vertically.
This question about horizontal subtotals, came from someone at YouTube.
And they just got to tell their boss.
Basically, here's how to do it vertically.
And it's much easier.
So, let's do it this way.
To subtotal, you need to sort the field you want to add subtotals to.
So, I've already sorted this.
And the subtotal in 2007, I go to the data ribbon and subtotal.
In earlier versions, you click in one cell and you...
The data menu subtotals, which is [ ALT+D+B ] And then it's very simple because I've already sorted.
I say at each change in months sums function subtotals at sales because that's what I want to add up.
And then click OK.
Just like that.
Boom!
Subtotals for January, Subtotals for February.
Now, here's what I did.
I didn't insert columns, but I added a totals row.
And I actually want to do a formula here.
That will put a blank in the cell if we're still at January.
But do a subtotal if we get to the last month for this period.
So here's the formula I did, equals if and a logical test is going to be one, two, three cells above.
Is this label not and not is less than greater than is that not equal to whatever the next label is.
That way when we get take this formula brrr over to hear the H column.
It's looking at label January, label Februari.
They are not equal So, that's where we want our subtotal.
Comma so, that that's the logical test.
The value is true which means these are not equal.
It's going to just be SUMIF.
So, [ =SUMIF ] And there's a range and a criteria.
The criteria goes with the range and then of course the screen tip says, sum_range.
Those are the things we want to add.
So, the range we're going to evaluate.
I'm going to click in this cell.
[ CTRL+SHIFT+right arrow ] then F4 to lock it and jump the screen back in view.
comma and the criteria is going to be one, two, three cells above because when we get over to this column right, we want to subtotal it.
All it needs to do is look at that label comma and then the sum range is this.
[ CTRL+SHIFT+right arrow and F4 ] Close parentheses and notice the screen tip tells us oh.
Oh, that's our value for true for the if formula.
So, we type a comma and the value if false is going to course be Double-quote, double-quote for blank.
Close [ parenthesis ] [ CTRL+ENTER ] Now, I'm going to drag this over and it looks like it got our totals.
Now, one last thing.
Since I couldn't insert a column and do all that cool stuff, maybe I'll do some conditional formatting.
So, we get a color here and a color here, to visually indicate that these are the subtotals.
I'm going to highlight this whole row here.
Use the scroll arrow to come back and watch this.
I'm going to hold CTRL and click right here.
Drag all the way to the end.
Actually, let me do that a much cooler way.
Watch this [ CTRL+SHIFT+right-arrow ] and then I'm going to scroll back.
Holding CTRL, I'm going to click here.
And then after I've clicked there, I'm going to [ CTRL+SHIFT+right-arrow ] So, we can do that selection trick on non contiguous ranges.
We want to do conditional formatting.
The active cell is right there.
So we're going to do a true/false formula.
Now in 2007, you go to Home>Conditional Formatting >New Rules.
In earlier versions to go to the Format Menu, I'm going to use the keyboard shortcut [ ALT+O+D ], [ ALT+O+D ]...
In this version, you click on New Rule.
And then formula down here and click in this text box in earlier versions.
You click the first drop down and point to formula is.
Now, what is our true false formula.
We are going to be for all of these cells.
Where we're always going to ask is this.
But notice there's two dollar signs hit the F4 key because we want dollar sign just in front of the row.
So, when the formula in memory, the true false from that gets copied down here to this cell.
It's still looking at that.
January not ...
The next one and we need to lock that also.
In front of the row that's the true false formula.
I'm going to click Format.
I'm going to click fill.
That used to say patterns>red Font, I'm going to click on this white.
Because the value of the color red.
This doesn't you don't think of this as a dark color.
But the value of it is dark, and so I'm going to use...
Oops! I didn't get the right format.
The value is quite dark.
So, I'm going to select a light colored font.
Click OK.
I'll click OK and sure enough.
Now we have our conditional formatting.
Okay! so that's a horizontal Subtotals.
Hey, alright! mike those were great.
I love that idea of just having the subtitles down in the bottom row and also your comment.
If your manager can figure out how this works, he can do or settle Subtotals.
Otherwise go with vertical.
Of course this is so much easier to do vertical.
Alright! I want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel and Excel Is Fun.
