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.

Tagged : # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

8 thoughts on “Learn Excel – Year over Year % Growth – Podcast 2152”

Leave a Reply

Your email address will not be published. Required fields are marked *