The goal is to build a Year-over-Year report in a pivot table and show the % Growth
I almost always do the percentage growth calculation as a formula outside of the pivot table
But that has issues, such as GetPivotData and the formula needs to handle if the pivot table shrinks or grows
Using a technique that I learned from Tobias Ljung, you can build the calculation in to the pivot table.
Add revenue a second time to the pivot table.
The calculation is % Change from, Years, Previous Item
Hide the % change for the first year (they are all blank)
I almost always do the percentage growth calculation as a formula outside of the pivot table
But that has issues, such as GetPivotData and the formula needs to handle if the pivot table shrinks or grows
Using a technique that I learned from Tobias Ljung, you can build the calculation in to the pivot table.
Add revenue a second time to the pivot table.
The calculation is % Change from, Years, Previous Item
Hide the % change for the first year (they are all blank)
Transcript of the video:
Learn Excel From MrExcel, Podcast Episode 2152: Year Over Year Growth Percentage In A Pivot Table.
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
I was down in Dallas for Excelapalooza 6 doing 3 days of seminars down there and these two guys from Sweden were in my seminar -- Tobias and Robert.
Tobias has his own pivot table book that he's written, and he was in my pivot table seminar, and I was showing one way to do a year over year growth and Tobias had a better way to go.
So, let’s do this.
INSERT, PIVOT TABLE, OK.
We're going to put DATES down the left-hand side and I'm going to press CONTROL+Z to go back to daily dates like that, and then REVENUE, and we'll choose the very first DATE field.
This was the behavior in Excel 2010, Excel 2013.
GROUP FIELDS, say you want to group by MONTHS and YEARS.
Alright.
Works great.
Take the YEARS field and move it over to COLUMNS.
Now, we don’t want a GRAND TOTAL here, so we right-click and REMOVE GRAND TOTAL, and this is the point where people would like to see the growth, the percentage growth, of 2016 over 2015 -- this year versus last year -- and I always have to build this outside of the pivot table because I use grouping, so, therefore, it's illegal to create a CALCULATED ITEM.
Can't do this, alright?
So, I'm always stuck out here.
We have the GetPivotData problem and all that stuff, and Tobias says, oh, no, you can do that.
Just take the REVENUE field one more time, alright?
So, now we have SUM OF REVENUE and SUM OF REVENUE 2.
It’ll be like this.
So, we have 2015, 2016.
The SUM OF REVENUE, we're going to go to that field, double click, SHOW VALUES AS, and the calculation is going to be the % DIFFERENCE FROM, and the BASE FIELD is going to be YEAR, and the BASE ITEM is going to be PREVIOUS, alright?
So, for 2016, it's going to calculate this 287 ÷ 262 – 1.
It won't know what to do for 2015 because it doesn't have 2014 data.
So, that column is just going to appear as blank.
Click OK.
Simple enough.
Right click and HIDE.
Alright.
Now, the advantage of Tobias's version is that as this pivot table shrinks or grows -- let’s say that we added slicers and we only had a few months or something like that -- the calculation out here will shrink or grow with the pivot table, whereas mine, because the calculations are outside of the pivot table, it's just not going to work as well.
Now, hey, if you can read Swedish, by all means check out this book by Tobias on pivot tables.
Great book but, if you prefer books in English, check out my book Power Excel With MrExcel, the 2017 Edition.
Alright.
So, the goal is to build a year-over-year report in a pivot table and show the % growth.
I always do that calculation outside the pivot table but that has issues, like the GetPivotData problem, and the formula needs to handle if the pivot table shrinks or grows.
So, using this method from Tobias, you can build the calculation into the pivot table, add revenue as a second time, change that calculation to the % change from, YEARS, PREVIOUS item, and then you have to hide the extra column.
Great, great trick from Tobias.
Well, hey.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
I was down in Dallas for Excelapalooza 6 doing 3 days of seminars down there and these two guys from Sweden were in my seminar -- Tobias and Robert.
Tobias has his own pivot table book that he's written, and he was in my pivot table seminar, and I was showing one way to do a year over year growth and Tobias had a better way to go.
So, let’s do this.
INSERT, PIVOT TABLE, OK.
We're going to put DATES down the left-hand side and I'm going to press CONTROL+Z to go back to daily dates like that, and then REVENUE, and we'll choose the very first DATE field.
This was the behavior in Excel 2010, Excel 2013.
GROUP FIELDS, say you want to group by MONTHS and YEARS.
Alright.
Works great.
Take the YEARS field and move it over to COLUMNS.
Now, we don’t want a GRAND TOTAL here, so we right-click and REMOVE GRAND TOTAL, and this is the point where people would like to see the growth, the percentage growth, of 2016 over 2015 -- this year versus last year -- and I always have to build this outside of the pivot table because I use grouping, so, therefore, it's illegal to create a CALCULATED ITEM.
Can't do this, alright?
So, I'm always stuck out here.
We have the GetPivotData problem and all that stuff, and Tobias says, oh, no, you can do that.
Just take the REVENUE field one more time, alright?
So, now we have SUM OF REVENUE and SUM OF REVENUE 2.
It’ll be like this.
So, we have 2015, 2016.
The SUM OF REVENUE, we're going to go to that field, double click, SHOW VALUES AS, and the calculation is going to be the % DIFFERENCE FROM, and the BASE FIELD is going to be YEAR, and the BASE ITEM is going to be PREVIOUS, alright?
So, for 2016, it's going to calculate this 287 ÷ 262 – 1.
It won't know what to do for 2015 because it doesn't have 2014 data.
So, that column is just going to appear as blank.
Click OK.
Simple enough.
Right click and HIDE.
Alright.
Now, the advantage of Tobias's version is that as this pivot table shrinks or grows -- let’s say that we added slicers and we only had a few months or something like that -- the calculation out here will shrink or grow with the pivot table, whereas mine, because the calculations are outside of the pivot table, it's just not going to work as well.
Now, hey, if you can read Swedish, by all means check out this book by Tobias on pivot tables.
Great book but, if you prefer books in English, check out my book Power Excel With MrExcel, the 2017 Edition.
Alright.
So, the goal is to build a year-over-year report in a pivot table and show the % growth.
I always do that calculation outside the pivot table but that has issues, like the GetPivotData problem, and the formula needs to handle if the pivot table shrinks or grows.
So, using this method from Tobias, you can build the calculation into the pivot table, add revenue as a second time, change that calculation to the % change from, YEARS, PREVIOUS item, and then you have to hide the extra column.
Great, great trick from Tobias.
Well, hey.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
