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.

Thank you

Thanks Bill, very Helpful 🙂

Ctrl + Z to undo auto grouping in Excel 2016 is radder than rad!!!!!

Great trick, very useful and informative.

Exactly the tip I was looking for! Thank you Mr. Excel!

You read my mind, Mr. Excel, the tip is just what I am looking for. Thanks a lot!!

Useful and informative.

I could not like this video twice …. illegal … 🙁