Bill 'MrExcel' Jelen was Live in New York City to Judge the ModelOff Worldwide Excel Championship! And With 16 Excel Rock Stars, from around the Globe, in NYC -- and Bill having access to them all -- why not do some Podcasting On-Site!
Bill says. "OK... you caught me. I was completely thrown off yesterday when Mack Wilk started using the Grouping Function in Excel. Today, I get him to explain Grouping again." Follow along with Bill and Mack Wilk in Episode #1617 as they go deeper into the elasticity of Grouping in Microsoft Excel.
ModelOff is a worldwide competition to find the best Excel modellers with a $25,000 cash prize. For information on next year's competition, visit http://www.modeloff.com/modeloff-2013/
...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 says. "OK... you caught me. I was completely thrown off yesterday when Mack Wilk started using the Grouping Function in Excel. Today, I get him to explain Grouping again." Follow along with Bill and Mack Wilk in Episode #1617 as they go deeper into the elasticity of Grouping in Microsoft Excel.
ModelOff is a worldwide competition to find the best Excel modellers with a $25,000 cash prize. For information on next year's competition, visit http://www.modeloff.com/modeloff-2013/
...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:
All right, I don't use proving it well at all I can't ever figure out how the thing works and so I just started talking to Mack.
I said well that's cool, I don't use grouping and he starts showing me all these cool tricks.
I was like wait we gotta turn the thing back on and you got to show us how to do this.
So, this is called MrExcel Learns how to Group. Mack.
Mack: Okay! So, I'm back on vision when I construct financial model I use a cascading structure.
For example, we can start with the first category which is Revenues and below this line, I will model all the revenues of the company, let's say revenues, Revenues and this will be the sum of Product 1 and so and Product 2.
Yeah, and then we'll start with second category, which will be called Product 1 and here we'll have Revenues and Volume and Price, not prive, Price and naturally to keep it simple we'll, we'll just limit the calculation of Revenues as the multiplication of Volume and Price.
Not, SUM it should be the PRODUCT.
We can add the second element, which is Product 2 and the calculation is the same.
Well, actually here we also can use the SUMIF, but we don't have to do that we can just link the both things.
Yeah, and now the thing about the grouping.
we can group all this, this, this area as shown, before and we can change the settings of Excel, to change that the, the, the way that the Excel should groups the rows.
This is not very user friendly when we change the output settings, the rows will be grouped from-- to the top, from down to the top, not from the top to the empty down.
Bill Jelen: Okay.
Mack: And we can also group subcategories.
Bill Jelen: So, you're actually creating groups inside of groups.
Mack: Yes and that's why right now, I can have only the summary of my, of my products.
I can close all the older section and it gets better when we add another, another level, another level and here we can-- For example, model volume and separately, we can we can also model bronze let's say volume will be volume in year, in the previous year and here will be percentage change.
So, Volume in the previous year was, was 100.
This will be 0 and this will increase by 10% each year.
Watch this, let's do something.
So, this will want the formula anymore and this will be...
Okay.
So, we can link the the volume to the subcategory.
Bill Jelen: Right.
Mack: And here we have it.
So, three levels of the, of the structure level 3, level 2, level 1.
Bill Jelen: Wait, that's cool.
All right! Grouping, I always use grouping as part of automatic subtotals, but I'm never using grouping just alone.
All right, let's see if I got this.
So, we want to collapse, we're going be able to collapse the details of the section.
So, I choose all of those rows 4, 5, 6 & 7 and come out here and group alright, but the problem that we have initially is that they're grouping to the bottom.
So, it doesn't work well, right and the thing that I had never seen before his Mack came out here to the Outline Group, click the Dialog Launcher.
Oh! check this out and un-checks summary rows below detail, which takes this existing group, but it moves the minus sign to the top, click OK.
All right! So now, we actually have something where we can collapse down to the title or expand to show the details.
Title, Details, Title, Details straight down here see make sure we got it once that settings been changed.
It's going to work automatically.
So now, I can collapse to the title and details to shows me everything 1 shows me just the section headings.
So, manual grouping great way to go.
When I've come back and watch the video just to figure out exactly which things to group, but that's a cool trick thanks to Mack we're here all the way from Poland.
Good luck tomorrow The Model Off's finals thank you.
Want to thank everyone for stopping by.
We'll see you next time for another net cast from MrExcel.
I said well that's cool, I don't use grouping and he starts showing me all these cool tricks.
I was like wait we gotta turn the thing back on and you got to show us how to do this.
So, this is called MrExcel Learns how to Group. Mack.
Mack: Okay! So, I'm back on vision when I construct financial model I use a cascading structure.
For example, we can start with the first category which is Revenues and below this line, I will model all the revenues of the company, let's say revenues, Revenues and this will be the sum of Product 1 and so and Product 2.
Yeah, and then we'll start with second category, which will be called Product 1 and here we'll have Revenues and Volume and Price, not prive, Price and naturally to keep it simple we'll, we'll just limit the calculation of Revenues as the multiplication of Volume and Price.
Not, SUM it should be the PRODUCT.
We can add the second element, which is Product 2 and the calculation is the same.
Well, actually here we also can use the SUMIF, but we don't have to do that we can just link the both things.
Yeah, and now the thing about the grouping.
we can group all this, this, this area as shown, before and we can change the settings of Excel, to change that the, the, the way that the Excel should groups the rows.
This is not very user friendly when we change the output settings, the rows will be grouped from-- to the top, from down to the top, not from the top to the empty down.
Bill Jelen: Okay.
Mack: And we can also group subcategories.
Bill Jelen: So, you're actually creating groups inside of groups.
Mack: Yes and that's why right now, I can have only the summary of my, of my products.
I can close all the older section and it gets better when we add another, another level, another level and here we can-- For example, model volume and separately, we can we can also model bronze let's say volume will be volume in year, in the previous year and here will be percentage change.
So, Volume in the previous year was, was 100.
This will be 0 and this will increase by 10% each year.
Watch this, let's do something.
So, this will want the formula anymore and this will be...
Okay.
So, we can link the the volume to the subcategory.
Bill Jelen: Right.
Mack: And here we have it.
So, three levels of the, of the structure level 3, level 2, level 1.
Bill Jelen: Wait, that's cool.
All right! Grouping, I always use grouping as part of automatic subtotals, but I'm never using grouping just alone.
All right, let's see if I got this.
So, we want to collapse, we're going be able to collapse the details of the section.
So, I choose all of those rows 4, 5, 6 & 7 and come out here and group alright, but the problem that we have initially is that they're grouping to the bottom.
So, it doesn't work well, right and the thing that I had never seen before his Mack came out here to the Outline Group, click the Dialog Launcher.
Oh! check this out and un-checks summary rows below detail, which takes this existing group, but it moves the minus sign to the top, click OK.
All right! So now, we actually have something where we can collapse down to the title or expand to show the details.
Title, Details, Title, Details straight down here see make sure we got it once that settings been changed.
It's going to work automatically.
So now, I can collapse to the title and details to shows me everything 1 shows me just the section headings.
So, manual grouping great way to go.
When I've come back and watch the video just to figure out exactly which things to group, but that's a cool trick thanks to Mack we're here all the way from Poland.
Good luck tomorrow The Model Off's finals thank you.
Want to thank everyone for stopping by.
We'll see you next time for another net cast from MrExcel.
