Bill brings us another Podcast in the "Things that I Learned at Trainertage" Series. [To get a background on 'Trainertage 2013, see the first episode in this new series: "Dynamic Range Without OFFSET" Podcast #1632]
Bill really likes Formatting. Today, in Episode #1643, MrExcel takes us on a tour of Formatting in Microsoft Excel that is likely to change the way you see your data from now forward.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Bill really likes Formatting. Today, in Episode #1643, MrExcel takes us on a tour of Formatting in Microsoft Excel that is likely to change the way you see your data from now forward.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
Excel tricks that I learned at Trainertage.
Episode 1643: formatting subtotal rows.
All right, hey I love subtotals.
I talk about subtotalss all the time and I have lots of different tricks for formatting the subtotal rows but this one is better.
So back here on the Data tab where the subtotal is we're going to go to the outline dialogue launcher, Automatic styles.
This is so cool.
Click OK.
Are you ready?
Data, subtotal, at each change in region use the SUM function.
Bam, bam, bam.
We’ve talked about subtotals many times before.
I'm going to do it again.
Data, subtotal, at each change in product uncheck Replace Current Subtotals, click OK.
One more time: data, subtotal, each change in customer-- of course I previously sorted this by region, product, and customer.
Click OK.
Now we have a whole bunch of levels of subtotals but because we checked this box first, Excel created some new cell styles for us: Home, Cell Styles, RowLevel_1, RowLevel_2, RowLevel_3, RowLevel_4.
So let's just choose one of these.
All right, click and modify.
So RowLevel_2 I'm going to format that to have a fill of orange.
Click OK, Click OK and now you'll see that all of the region totals are automatically in orange.
Let's go back to number 5.
Cell Styles, RowLevel_3, let's do RowLevel_4, modify, we’ll change that to have a background color of green.
Click OK, click OK, there you go.
Look at that automatically formatting those subtotal rows.
I was pretty jazzed when I saw this because I said this is going to be amazing.
What we're going to be able to do is now that we know these words for Cell Styles: RowLevel_1, RowLevel_2, RowLevel_3, I can create a template book .xltx that has these predefined and then when I create the subtotals everything is going to be great but there is a massive bug.
Microsoft documents this trick but it's like they don't know that the trick is really theirs.
So let's sort by customer, click A-Z, Data, Subtotal, at each change in customer use the SUM function, click OK.
All right, every single time if the cell style existed beforehand, right here, so it existed before you did the subtotals then we're going to get a complete black line every single time.
Some massive bug.
I don't think anyone knew that this trick was there.
It's so hidden.
I'm sure some people know it was there.
Certainly Tanja who showed me the trick knew it was there.
So it's like no one's using this.
Somewhere this bug crept in and now Microsoft-- I already know.
We're going to tell them and they're going to say, “Won't fix.
Won't fix.” It's my least favorite two words in the English language but we'll see.
I'll pass along and see what happens.
Still a good trick.
Fast way if you have lots of subtotal rows to format those using cell styles.
It would just be so much better if it worked all of the time.
All right. Hey, my thanks to Tanja Kuhn.
Tanja is the video to brain Excel 2010 pivot table star, that video recording, also the creator of Trainertage.
I want to thank Tanja for inviting me this year and then I'll be there next year January 17th through the 21st in beautiful blue circles.
Hey, I want to thank you stopping by.
We'll see you next time for another netcast from MrExcel.
Episode 1643: formatting subtotal rows.
All right, hey I love subtotals.
I talk about subtotalss all the time and I have lots of different tricks for formatting the subtotal rows but this one is better.
So back here on the Data tab where the subtotal is we're going to go to the outline dialogue launcher, Automatic styles.
This is so cool.
Click OK.
Are you ready?
Data, subtotal, at each change in region use the SUM function.
Bam, bam, bam.
We’ve talked about subtotals many times before.
I'm going to do it again.
Data, subtotal, at each change in product uncheck Replace Current Subtotals, click OK.
One more time: data, subtotal, each change in customer-- of course I previously sorted this by region, product, and customer.
Click OK.
Now we have a whole bunch of levels of subtotals but because we checked this box first, Excel created some new cell styles for us: Home, Cell Styles, RowLevel_1, RowLevel_2, RowLevel_3, RowLevel_4.
So let's just choose one of these.
All right, click and modify.
So RowLevel_2 I'm going to format that to have a fill of orange.
Click OK, Click OK and now you'll see that all of the region totals are automatically in orange.
Let's go back to number 5.
Cell Styles, RowLevel_3, let's do RowLevel_4, modify, we’ll change that to have a background color of green.
Click OK, click OK, there you go.
Look at that automatically formatting those subtotal rows.
I was pretty jazzed when I saw this because I said this is going to be amazing.
What we're going to be able to do is now that we know these words for Cell Styles: RowLevel_1, RowLevel_2, RowLevel_3, I can create a template book .xltx that has these predefined and then when I create the subtotals everything is going to be great but there is a massive bug.
Microsoft documents this trick but it's like they don't know that the trick is really theirs.
So let's sort by customer, click A-Z, Data, Subtotal, at each change in customer use the SUM function, click OK.
All right, every single time if the cell style existed beforehand, right here, so it existed before you did the subtotals then we're going to get a complete black line every single time.
Some massive bug.
I don't think anyone knew that this trick was there.
It's so hidden.
I'm sure some people know it was there.
Certainly Tanja who showed me the trick knew it was there.
So it's like no one's using this.
Somewhere this bug crept in and now Microsoft-- I already know.
We're going to tell them and they're going to say, “Won't fix.
Won't fix.” It's my least favorite two words in the English language but we'll see.
I'll pass along and see what happens.
Still a good trick.
Fast way if you have lots of subtotal rows to format those using cell styles.
It would just be so much better if it worked all of the time.
All right. Hey, my thanks to Tanja Kuhn.
Tanja is the video to brain Excel 2010 pivot table star, that video recording, also the creator of Trainertage.
I want to thank Tanja for inviting me this year and then I'll be there next year January 17th through the 21st in beautiful blue circles.
Hey, I want to thank you stopping by.
We'll see you next time for another netcast from MrExcel.
