Excel 2007 offers new pivot table functionality. Episode 652 shows you the details.
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:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
We're looking at Excel 2007, this week.
Take a look at pivot tables.
Now, pivot tables are always my favorite feature, in Excel.
They are still the most powerful feature, we have.
Although, they've added a bit of new functionality.
First of all, pivot tables are now found on the insert tab of the ribbon, into the first icon over there on the left.
Click [ pivot table ], choose where our ranges and click [ ok ] and then you'll notice, that they have changed the pivot table interface rather than dragging Fields from the field list, right over to the pivot table like we used to do.
You are now going to build the pivot table within the field list.
So, there's four drop zones down here.
Row labels and column labels.
That's exactly what it used to be SUM values, is the old data area and they rename the page area to be report filters.
So, basically when we check a field on the top for example, customer.
It automatically shows up along the row area and then when I check a numeric field for example, revenue.
It automatically shows up in SUM of revenue.
So, just a couple of check marks.
Now, rather than trying to drag the field to the right spot.
Now, let's say I would add product in.
Product automatically shows up along the row label area.
But it's very easy for me to take that and move it to be a column label.
Just by dragging and dropping fields within the drop zones.
Now, a few new things that they added to pivot tables.
We can now filter to specific customers.
It used to be that you could go through here and select you know, specific customers but now, we can kind of, build some virtual filters that says, hey!
We want all the customers that begin with a certain range, contain this word, don't contain this word.
That's a cool improvement.
The other thing, we have is let's say, we're going to take customer off and put the date field on.
Again, we have all sorts of new virtual date filters and provided your fields contain dates.
We could ask for the pivot table to always show us last week or next month or last month or this month.
This quarter or we could ask for all dates, in November.
So, lots of virtual fields there, that they've added to pivot tables.
Great new options, on the lighter side they also give us many more ways to to format the pivot table.
Now, for we were stuck with 14 options and frankly they were all just horrible.
So now, we can very easily apply different colors to the pivot table.
Let me choose this one.
I said we want to see banded rows, banded columns and you know very quickly format the pivot table using really...
There's about 25,000 variations now and most of them looked pretty good.
So, that's a little bit of the new features in pivot tables.
Stop back, tomorrow we'll take another look at Excel 2007.
I'm Bill Jelen.
We're looking at Excel 2007, this week.
Take a look at pivot tables.
Now, pivot tables are always my favorite feature, in Excel.
They are still the most powerful feature, we have.
Although, they've added a bit of new functionality.
First of all, pivot tables are now found on the insert tab of the ribbon, into the first icon over there on the left.
Click [ pivot table ], choose where our ranges and click [ ok ] and then you'll notice, that they have changed the pivot table interface rather than dragging Fields from the field list, right over to the pivot table like we used to do.
You are now going to build the pivot table within the field list.
So, there's four drop zones down here.
Row labels and column labels.
That's exactly what it used to be SUM values, is the old data area and they rename the page area to be report filters.
So, basically when we check a field on the top for example, customer.
It automatically shows up along the row area and then when I check a numeric field for example, revenue.
It automatically shows up in SUM of revenue.
So, just a couple of check marks.
Now, rather than trying to drag the field to the right spot.
Now, let's say I would add product in.
Product automatically shows up along the row label area.
But it's very easy for me to take that and move it to be a column label.
Just by dragging and dropping fields within the drop zones.
Now, a few new things that they added to pivot tables.
We can now filter to specific customers.
It used to be that you could go through here and select you know, specific customers but now, we can kind of, build some virtual filters that says, hey!
We want all the customers that begin with a certain range, contain this word, don't contain this word.
That's a cool improvement.
The other thing, we have is let's say, we're going to take customer off and put the date field on.
Again, we have all sorts of new virtual date filters and provided your fields contain dates.
We could ask for the pivot table to always show us last week or next month or last month or this month.
This quarter or we could ask for all dates, in November.
So, lots of virtual fields there, that they've added to pivot tables.
Great new options, on the lighter side they also give us many more ways to to format the pivot table.
Now, for we were stuck with 14 options and frankly they were all just horrible.
So now, we can very easily apply different colors to the pivot table.
Let me choose this one.
I said we want to see banded rows, banded columns and you know very quickly format the pivot table using really...
There's about 25,000 variations now and most of them looked pretty good.
So, that's a little bit of the new features in pivot tables.
Stop back, tomorrow we'll take another look at Excel 2007.
