Calculate % of parent item in a pivot table.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Pivot Table Data Crunching Chapter 3: % of Parent Well, hey welcome back to the MrExcel netcast. I'm Bill Jelen.
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 had, 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 then let's choose what, maybe Revenue, there we go.
All right, and I'm going to put Revenue out there a second time.
All right, so we have Revenue and Revenue, we're going over to field list now.
At this second revenue here, I want to change that and they gave us some great new ways to do this.
We go to Options Calculations and then Show values As.
There's options here for % of Column Total, which has been there forever but really, the Holy Grail here. 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 % of Parent Row.
% of 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 ofcourse 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.
And they gave us the % of Parent and then a few others that they gave us, they're just out of view. Here, Ranks, Smallest to Largest and Rank, Largest to Smallest.
Values.
So, the West is the 6th in ranking of all of those different.
So, those are the new calculations they added, for all of the calculations, they made it much much easier to find.
You know, if you ever need a % of Parent Row, you're going to be dancing up and down the aisle, next to your cubicle, just because that finally became very easy to do.
Hey, well I want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
Pivot Table Data Crunching Chapter 3: % of Parent Well, hey welcome back to the MrExcel netcast. I'm Bill Jelen.
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 had, 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 then let's choose what, maybe Revenue, there we go.
All right, and I'm going to put Revenue out there a second time.
All right, so we have Revenue and Revenue, we're going over to field list now.
At this second revenue here, I want to change that and they gave us some great new ways to do this.
We go to Options Calculations and then Show values As.
There's options here for % of Column Total, which has been there forever but really, the Holy Grail here. 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 % of Parent Row.
% of 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 ofcourse 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.
And they gave us the % of Parent and then a few others that they gave us, they're just out of view. Here, Ranks, Smallest to Largest and Rank, Largest to Smallest.
Values.
So, the West is the 6th in ranking of all of those different.
So, those are the new calculations they added, for all of the calculations, they made it much much easier to find.
You know, if you ever need a % of Parent Row, you're going to be dancing up and down the aisle, next to your cubicle, just because that finally became very easy to do.
Hey, well I want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
