Another improvement in Excel 2010 is in the pivot table calculation options. You can now calculate the rank of an item, or the % of the parent row! Episode 1079 shows you how.
This blog is the video 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 blog is the video 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, welcome back to the MrExcel netcast. I'm Bill Jelen.
Basically, you start out with massive amounts of data.
So, how we're going to analyze it. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. We'll continue our look at excel 2010 and the new features in excel 2010.
Yesterday, I talked about cool feature in Pivot table.
So, let's take a look at another feature, they've added to Pivot Table. So will come back here and do Pivot Table.
Click OK.
This time I'm going to put Region and Product down the left-hand side and Revenue in the heart of the pivot table.
But, I'm going to take Revenue and add it to the Pivot Table again, because I want to show percentage of total now.
That was always sort of hard to do, because you had to go into Field Settings, and then you Show Values As here.
I like the fact that they took the Show Values As and promoted it, right up here to the ribbon.
Show Values As, so we could say % of Column Total, and we get to see each item's contribution towards the total.
All right. That's been around for a long time, easier to find now.
But, the question that I always got, and that was very hard to solve, was how do I show that this ABC, this 766 thousand, what the percentage is of the central region?
And you could never do that in a pivot table before. It always had to be done outside the Pivot Table, you know, and that just creates its own problems.
Check this out! They've now added brand new items of % of Parent Row Total.
% of Parent Row Total.
So, now we have these three items. If I select them to see down here, we're at a 100.00% And so, that's each item's contribution, within the central region.
This 35.42 is the central regions contribution within its parent, which ofcourse, is the grand total, so very cool.
They're also, as I was looking through, let's see what else we have.
They now have something called Rank smallest to largest and Rank largest to smallest.
Let's take a look at that. It looks like a great improvement.
We'll undo Region and Product.
We'll put Customer down the left hand side, and now if I want to see each item's rank, will come here and say Show Values As, Rank Largest to Smallest based on Customer.
Click OK.
And now, we can see who our number 1 customer is.
Walmart and number 2 is General Motors.
Another great improvement to the calculation field.
Now ofcourse, here Sum of Revenue2, I never liked that total, we can always just come here and type something new like, Rank (Customer) and change those titles. So, lots of great new improvements coming in Excel 2010.
Today, we took a look at some more Pivot Table improvements.
Tomorrow, dueling Excel podcast with Mike Gervin.
We're going to take a look at some of the new functions, they added to allow us to calculate in excel 2010.
Thanks for stopping by. See you next time for another netcast from MrExcel.
Basically, you start out with massive amounts of data.
So, how we're going to analyze it. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. We'll continue our look at excel 2010 and the new features in excel 2010.
Yesterday, I talked about cool feature in Pivot table.
So, let's take a look at another feature, they've added to Pivot Table. So will come back here and do Pivot Table.
Click OK.
This time I'm going to put Region and Product down the left-hand side and Revenue in the heart of the pivot table.
But, I'm going to take Revenue and add it to the Pivot Table again, because I want to show percentage of total now.
That was always sort of hard to do, because you had to go into Field Settings, and then you Show Values As here.
I like the fact that they took the Show Values As and promoted it, right up here to the ribbon.
Show Values As, so we could say % of Column Total, and we get to see each item's contribution towards the total.
All right. That's been around for a long time, easier to find now.
But, the question that I always got, and that was very hard to solve, was how do I show that this ABC, this 766 thousand, what the percentage is of the central region?
And you could never do that in a pivot table before. It always had to be done outside the Pivot Table, you know, and that just creates its own problems.
Check this out! They've now added brand new items of % of Parent Row Total.
% of Parent Row Total.
So, now we have these three items. If I select them to see down here, we're at a 100.00% And so, that's each item's contribution, within the central region.
This 35.42 is the central regions contribution within its parent, which ofcourse, is the grand total, so very cool.
They're also, as I was looking through, let's see what else we have.
They now have something called Rank smallest to largest and Rank largest to smallest.
Let's take a look at that. It looks like a great improvement.
We'll undo Region and Product.
We'll put Customer down the left hand side, and now if I want to see each item's rank, will come here and say Show Values As, Rank Largest to Smallest based on Customer.
Click OK.
And now, we can see who our number 1 customer is.
Walmart and number 2 is General Motors.
Another great improvement to the calculation field.
Now ofcourse, here Sum of Revenue2, I never liked that total, we can always just come here and type something new like, Rank (Customer) and change those titles. So, lots of great new improvements coming in Excel 2010.
Today, we took a look at some more Pivot Table improvements.
Tomorrow, dueling Excel podcast with Mike Gervin.
We're going to take a look at some of the new functions, they added to allow us to calculate in excel 2010.
Thanks for stopping by. See you next time for another netcast from MrExcel.
