Excel Rev Up - Pivot Table 2010: Podcast #1340

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Feb 14, 2011.
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
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top