Hiro asks how to build a pivot table to show year over year growth. Episode 655 will show you the cool grouping functionality in pivot tables which make this very easy.
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.
Today we have a question sent in by Hero.
Hero's up in Washington State. He had a question from pivot table data crunching.
There's an example in there and basically showed how to create a year-over-year summer report.
I want to walk through the steps for that.
We can do it with the pivot table, but you have to go through a couple of obscure tricks.
So we have a data set here that has data from both 2006 and 2007.
We're going to go to "Data" "Pivot Table" "Finish" and basically put the date along the left hand side and then maybe "Revenue" in the heart of the pivot table.
Now we want to group those daily dates, group those daily dates up to both, years and months.
So I'm going to go to the first daily date right click and choose "Group and Show Detail" and then "Group" and say that I want to group it up to both months and years.
You never want to choose just months in this dialog box because what it'll do is, it'll take January of this year, January of last year and put it together into a single row called January, which really never made any sense to me so what we're going to do.
Now you'll see that what's interesting is, where we initially had one field along the left-hand side, just the date field, we now have dates and years.
And it's interesting that excel does this breaking things out into two different fields, because now we can take the year field, and move it across the top so that way we can see year versus year what the revenue was.
Now I'm going to take the grand total off.
So we'll go to the "Pivot Table" "Table Options" and remove "Grand totals for rows" So we have 2006 - 2007.
Now the example in the book then went through and said well wouldn't be interesting to show maybe a year-over-year comparison.
So let me take the revenue there and add it, a second time move the data field, so that way it's a second column out here, and we can say, you know, show it as a percentage of the previous year or something like that.
I click the "Field Settings" button. I'll say % Growth, and then click the "Options" tab.
The "Options" tab gives us a whole bunch of more settings so we can say that we want to show it as a % Of In the years field, the previous item.
So click "OK".
And now what we get over here is the 2007 is showing us the percentage growth from the previous year.
Now if we had data in here for 2005, the calculation would work okay for 2006.
But you basically get the idea of how you can take daily dates in the span several years, group them upto months and years and then move the years to be a column field and be able to show year-over-year growth.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
you
Today we have a question sent in by Hero.
Hero's up in Washington State. He had a question from pivot table data crunching.
There's an example in there and basically showed how to create a year-over-year summer report.
I want to walk through the steps for that.
We can do it with the pivot table, but you have to go through a couple of obscure tricks.
So we have a data set here that has data from both 2006 and 2007.
We're going to go to "Data" "Pivot Table" "Finish" and basically put the date along the left hand side and then maybe "Revenue" in the heart of the pivot table.
Now we want to group those daily dates, group those daily dates up to both, years and months.
So I'm going to go to the first daily date right click and choose "Group and Show Detail" and then "Group" and say that I want to group it up to both months and years.
You never want to choose just months in this dialog box because what it'll do is, it'll take January of this year, January of last year and put it together into a single row called January, which really never made any sense to me so what we're going to do.
Now you'll see that what's interesting is, where we initially had one field along the left-hand side, just the date field, we now have dates and years.
And it's interesting that excel does this breaking things out into two different fields, because now we can take the year field, and move it across the top so that way we can see year versus year what the revenue was.
Now I'm going to take the grand total off.
So we'll go to the "Pivot Table" "Table Options" and remove "Grand totals for rows" So we have 2006 - 2007.
Now the example in the book then went through and said well wouldn't be interesting to show maybe a year-over-year comparison.
So let me take the revenue there and add it, a second time move the data field, so that way it's a second column out here, and we can say, you know, show it as a percentage of the previous year or something like that.
I click the "Field Settings" button. I'll say % Growth, and then click the "Options" tab.
The "Options" tab gives us a whole bunch of more settings so we can say that we want to show it as a % Of In the years field, the previous item.
So click "OK".
And now what we get over here is the 2007 is showing us the percentage growth from the previous year.
Now if we had data in here for 2005, the calculation would work okay for 2006.
But you basically get the idea of how you can take daily dates in the span several years, group them upto months and years and then move the years to be a column field and be able to show year-over-year growth.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
you
