Episode #1280, Bill takes a look at setting up a Pivot Table and then Calculating the 'Percent of a Parent Row' -a new feature in Excel 2010. This podcast inspired by Chapter 3 or "Pivot Table Data Crunching: Microsoft Excel 2010" by Bill Jelen
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Pivot Table The Data Crunching, Chapter 3: Percentage of Parent.
Oh hey, welcome back to the MrExcel netcast. I'm bill John. We're working through the Pivot Table Data Crunching for Excel 2010 book chapter 3. I wanted to talk about this great new feature that they added for us in Excel 2010. So, I'm going to insert a pivot table here.
Click OK. All right and I want to choose two different fields down the left hand side, region and market and they let's choose what may be Revenue and there you go.
All right and I'm gonna put Revenue out there a second time. All right, so we have Revenue and Revenue. We're going to the field list. Now, this second Revenue here, I want to change that and they gave us some great new ways to do this. We go to Options > Calculation and then show values as. There's options here for percentage of column total, which has been there forever but really, the Holy Grail here are the things that people have always wanted to do is... okay!
So, the West is 7.29% of the total.
California is what percentage of the West and that was really hard to do in the old versions of Excel. You had to do this really intense calculation in the original data set, in order to be able to add that up but check this out. Now, we have in Excel 2010. Show values as something called percentage of Parent Row, Percentage of the Parent Row total. So, California is 95% of the West.
Seattle is 4% of the West.
All right, now we still have the West being 7.29% of its parent, which of course is the grand total.
All right so they added both of those calculations.
Well, first of all they made it much easier to find these. Adding it as a drop-down gave us the percentage of parent and then a few others that they gave us that they're just out of a view here. Rank smallest to largest and rank largest to smallest values. So, the West is the sixth in ranking of all of those different. So, those are the new calculations they added for all of the calculations that made it much much easier to find. You know, if you ever need a percentage of parent row you're going to be dancing up and down the aisle next to your cubicle just because that finally, becomes very easy to do.
Hey, I wanna thank you for stopping by.
See you next time for another netcast from MrExcel.
Pivot Table The Data Crunching, Chapter 3: Percentage of Parent.
Oh hey, welcome back to the MrExcel netcast. I'm bill John. We're working through the Pivot Table Data Crunching for Excel 2010 book chapter 3. I wanted to talk about this great new feature that they added for us in Excel 2010. So, I'm going to insert a pivot table here.
Click OK. All right and I want to choose two different fields down the left hand side, region and market and they let's choose what may be Revenue and there you go.
All right and I'm gonna put Revenue out there a second time. All right, so we have Revenue and Revenue. We're going to the field list. Now, this second Revenue here, I want to change that and they gave us some great new ways to do this. We go to Options > Calculation and then show values as. There's options here for percentage of column total, which has been there forever but really, the Holy Grail here are the things that people have always wanted to do is... okay!
So, the West is 7.29% of the total.
California is what percentage of the West and that was really hard to do in the old versions of Excel. You had to do this really intense calculation in the original data set, in order to be able to add that up but check this out. Now, we have in Excel 2010. Show values as something called percentage of Parent Row, Percentage of the Parent Row total. So, California is 95% of the West.
Seattle is 4% of the West.
All right, now we still have the West being 7.29% of its parent, which of course is the grand total.
All right so they added both of those calculations.
Well, first of all they made it much easier to find these. Adding it as a drop-down gave us the percentage of parent and then a few others that they gave us that they're just out of a view here. Rank smallest to largest and rank largest to smallest values. So, the West is the sixth in ranking of all of those different. So, those are the new calculations they added for all of the calculations that made it much much easier to find. You know, if you ever need a percentage of parent row you're going to be dancing up and down the aisle next to your cubicle just because that finally, becomes very easy to do.
Hey, I wanna thank you for stopping by.
See you next time for another netcast from MrExcel.
