Today, in Episode #1340, Bill looks at Pivot Tables in Excel 2010. While reviewing new features in Excel 2010, learn how to fill in blanks in the row area, change a calculation to % of parent row, create Rank results, and learn about Slicers
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Rev up to Excel 2010, chapter 20.
Pivot Tables.
Hey, all right! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Now, you seen my podcast for a while, you know that I think Pivot tables are the coolest feature in Excel.
We could go on for hours talking about pivot tables and the book actually does go on and talk about pivot tables a lot.
But let's just show off a few of the brand new features in Excel 2010.
So, I'm gonna choose my data here, create pivot table, click [ OK ] and I get a brand new report.
Blank report over here, a list of fields and then four drop zones, report filter, row labels, column labels, and then sum values.
You can't see sum values, it's down off the screen.
Let's say we want to build a report showing product as a row label and region as a row label.
Also show revenue as a value.
I'm gonna put revenue there, a second time. I'll show you why in a second?
and also profit as a value up here.
Let's go to the pivot table field list, right now a couple of things starting Excel 2007.
They introduced this new compact layout and I hate the compact layout.
I always go to the design tab and go back to report layout, tabular to go back to the old Excel 2003, view of the pivot table, to me it's really important that product and region exist in two different columns because most of the time I'm going to copy this data and paste it as values and reuse the data.
All right! First feature in Excel 2010 that I love, if we want to fill in these blanks cells out here that was always tough to do before kind of use go to special visible cells or go to special blanks to do that check this out report layout, repeat all item labels, BAM!
There it is, fills in those blank cells.
That's something that's been driving me crazy forever, finally and finished.
Another thing that they added in Excel 2010 is the ability to change this calculation.
So, come back here to options and calculations.
We have summarized values by, which offers the 11 features that have always been there, sum, count, average, max, min, product, variance, standard deviation, but what I'm really interested in is under show values as.
These were always buried, where no one can find them.
They're much easier to find out and they added some really nice ones such as percentage of parent row total.
We choose that and you'll see that now, what happens is we see that the East is 34.37% of A129, total.
Here this 16% is the percentage of the grand total.
All right! So, always very, very hard to do before, now very easy to do, also possible we can show values as a rank, smallest to largest, largest to smallest Region and you say ranks everything.
So, those new calculations are brand new in Excel 2010 and then of course, the one that everyone is talking about is the ability to filter this, using a visual filter called a slicer.
So, I'll insert a slicer based on let's say customer, click [ OK ], and we get a list of all the customers.
I can change this to show instead of one column, several columns.
We'll make a little bit wider of course, you wonder arranges that way, what's above your pivot table.
Just simply choose from the slider and everything will update.
If you want to see it for one customer, there's one customer.
A whole bunch of customers, easy to use the sliders.
Now, there's a lot more with pivot tables again as I said, I can go on for an hour.
I've done lots of different podcasts about the pivot table, just want to show you some of the nice things that have been improved in Excel 2010, pivot tables.
Hey! I want to thank you for stopping by, we'll see next time for another netcast from MrExcel.
Rev up to Excel 2010, chapter 20.
Pivot Tables.
Hey, all right! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Now, you seen my podcast for a while, you know that I think Pivot tables are the coolest feature in Excel.
We could go on for hours talking about pivot tables and the book actually does go on and talk about pivot tables a lot.
But let's just show off a few of the brand new features in Excel 2010.
So, I'm gonna choose my data here, create pivot table, click [ OK ] and I get a brand new report.
Blank report over here, a list of fields and then four drop zones, report filter, row labels, column labels, and then sum values.
You can't see sum values, it's down off the screen.
Let's say we want to build a report showing product as a row label and region as a row label.
Also show revenue as a value.
I'm gonna put revenue there, a second time. I'll show you why in a second?
and also profit as a value up here.
Let's go to the pivot table field list, right now a couple of things starting Excel 2007.
They introduced this new compact layout and I hate the compact layout.
I always go to the design tab and go back to report layout, tabular to go back to the old Excel 2003, view of the pivot table, to me it's really important that product and region exist in two different columns because most of the time I'm going to copy this data and paste it as values and reuse the data.
All right! First feature in Excel 2010 that I love, if we want to fill in these blanks cells out here that was always tough to do before kind of use go to special visible cells or go to special blanks to do that check this out report layout, repeat all item labels, BAM!
There it is, fills in those blank cells.
That's something that's been driving me crazy forever, finally and finished.
Another thing that they added in Excel 2010 is the ability to change this calculation.
So, come back here to options and calculations.
We have summarized values by, which offers the 11 features that have always been there, sum, count, average, max, min, product, variance, standard deviation, but what I'm really interested in is under show values as.
These were always buried, where no one can find them.
They're much easier to find out and they added some really nice ones such as percentage of parent row total.
We choose that and you'll see that now, what happens is we see that the East is 34.37% of A129, total.
Here this 16% is the percentage of the grand total.
All right! So, always very, very hard to do before, now very easy to do, also possible we can show values as a rank, smallest to largest, largest to smallest Region and you say ranks everything.
So, those new calculations are brand new in Excel 2010 and then of course, the one that everyone is talking about is the ability to filter this, using a visual filter called a slicer.
So, I'll insert a slicer based on let's say customer, click [ OK ], and we get a list of all the customers.
I can change this to show instead of one column, several columns.
We'll make a little bit wider of course, you wonder arranges that way, what's above your pivot table.
Just simply choose from the slider and everything will update.
If you want to see it for one customer, there's one customer.
A whole bunch of customers, easy to use the sliders.
Now, there's a lot more with pivot tables again as I said, I can go on for an hour.
I've done lots of different podcasts about the pivot table, just want to show you some of the nice things that have been improved in Excel 2010, pivot tables.
Hey! I want to thank you for stopping by, we'll see next time for another netcast from MrExcel.
