Creating your first pivot table in Excel 2007.
Episode 846 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!
Episode 846 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!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, out on Facebook, I was setting up this new group: Excel Pivot Tables Rock!
If you're a Facebook user, I encourage you to come out here.
I was uploading some videos and I realized I didn't really have a good video that shows from scratch how to start your first Pivot Table if you're using Excel 2007.
And I had covered that in Excel 2003, and then when 2007 came up, I showed, you know how it was different, but I never showed for someone who's brand new in Excel 2007, how did you start and create a Pivot Table.
So a Pivot Table, one of the most powerful features we have in Excel, allows you to take 500, 5000, or 500,000 rows of data and summarize it in about 6 clicks.
You want to start out with a really basic data set, heading above every column, no blank rows, no blank columns, no months going across the columns-- just ugly transactional data.
We go to the Insert tab of the ribbon and click the PivotTable icon over here on the left-hand side, and provided your data is in Excel, you have no blank rows, no blank columns, it's going to get this question right-- you just click OK.
And we end up with a blank Pivot Table on the left hand side, and on the right hand side a Pivot Table field list.
Now, in the old days, we used to take fields from the field list and drag them onto the report.
We don't have to do that anymore; we now have two sections at the top-- the list of fields and at the bottom for drop zones-- and we simply take the fields from the top and drag them to the right space on the bottom.
So, let's say we need to build a summary of sales by Region and Product.
Well, as soon as I click Region, it automatically goes to Column A and then it shows up down here in the Row Labels field.
I'll take the Product field, instead of clicking it I'm going to drag over to the Column Labels-- that'll put our Products going across the top.
And then choose whatever we need-- Revenue, Cost of Goods Sold, or Profit.
Since it's a numeric field it automatically goes right to the Sum Values area.
We've taken that data set now, without creating a single formula, created a beautiful view of the data.
And if your manager wants you to rearrange this, we just take the fields here; Product, from column to row; Region, from row to column; and we have a brand new report.
Or maybe we want to add the Customer field in so we can see which Customers bought which Products-- easy to do-- or drive the Customer field to the top of the row Labels list and now we'll see which Products were purchased by which Customer-- subtle difference.
One new thing in Excel 2007 is the great new Auto Formats, and we have them back here on the Design tab, so you can choose variety of different Formats and choose whether we want to show banded rows or banded columns so you make a great looking Pivot Table in just a few clicks.
Well, I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
[ music ]
Well, out on Facebook, I was setting up this new group: Excel Pivot Tables Rock!
If you're a Facebook user, I encourage you to come out here.
I was uploading some videos and I realized I didn't really have a good video that shows from scratch how to start your first Pivot Table if you're using Excel 2007.
And I had covered that in Excel 2003, and then when 2007 came up, I showed, you know how it was different, but I never showed for someone who's brand new in Excel 2007, how did you start and create a Pivot Table.
So a Pivot Table, one of the most powerful features we have in Excel, allows you to take 500, 5000, or 500,000 rows of data and summarize it in about 6 clicks.
You want to start out with a really basic data set, heading above every column, no blank rows, no blank columns, no months going across the columns-- just ugly transactional data.
We go to the Insert tab of the ribbon and click the PivotTable icon over here on the left-hand side, and provided your data is in Excel, you have no blank rows, no blank columns, it's going to get this question right-- you just click OK.
And we end up with a blank Pivot Table on the left hand side, and on the right hand side a Pivot Table field list.
Now, in the old days, we used to take fields from the field list and drag them onto the report.
We don't have to do that anymore; we now have two sections at the top-- the list of fields and at the bottom for drop zones-- and we simply take the fields from the top and drag them to the right space on the bottom.
So, let's say we need to build a summary of sales by Region and Product.
Well, as soon as I click Region, it automatically goes to Column A and then it shows up down here in the Row Labels field.
I'll take the Product field, instead of clicking it I'm going to drag over to the Column Labels-- that'll put our Products going across the top.
And then choose whatever we need-- Revenue, Cost of Goods Sold, or Profit.
Since it's a numeric field it automatically goes right to the Sum Values area.
We've taken that data set now, without creating a single formula, created a beautiful view of the data.
And if your manager wants you to rearrange this, we just take the fields here; Product, from column to row; Region, from row to column; and we have a brand new report.
Or maybe we want to add the Customer field in so we can see which Customers bought which Products-- easy to do-- or drive the Customer field to the top of the row Labels list and now we'll see which Products were purchased by which Customer-- subtle difference.
One new thing in Excel 2007 is the great new Auto Formats, and we have them back here on the Design tab, so you can choose variety of different Formats and choose whether we want to show banded rows or banded columns so you make a great looking Pivot Table in just a few clicks.
Well, I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
[ music ]
