MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Year over Year % Growth


October 04, 2017 - by Bill Jelen

Excel Pivot Table technique to calculate year over year growth percentage for each product, each customer, or anything.

Watch Video

  • A viewer downloads data from a system where each item is separated by Alt+Enter
  • Need to VLOOKUP each item in the cell
  • Use Power Query to split the items out, do VLOOKUP, then join them back together
  • This is like doing a ConcatenateX in DAX (which does not exist) or a TEXTJOIN(CHAR(10) in Office 365 Excel
  • Built a solution using Power Query, including the Structured Column tool of Extract As
  • That feature only works on a list, not a table, so I used the Table.Column function to convert the table to a list.

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2152 year-over-year growth
  • percentage in a pivot table hey welcome
  • back to the MrExcel net cast I'm Bill
  • Jalen I was down in Dallas for Axela
  • Palooza six doing three 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 my
  • pivot table seminar and I was showing
  • one way to do a year of your growth and
  • Tobias had a better way to go so let's
  • do this insert pivot table okay we're
  • gonna put dates down the left-hand side
  • and I'm gonna press ctrl-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 they want
  • to group by months and years all right
  • 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 of 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 all right
  • so I'm always stuck out here we have the
  • get pivot data 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 some of
  • revenue and some of revenue to a little
  • bit like this so we have 2015 2016 this
  • summer revenue - we're gonna go to that
  • field double click show values as and
  • the calculation is going to be the
  • percentage difference from and the base
  • field is going to be year and the base
  • item is going to be previous all right
  • so for 2016 it's going to calculate this
  • 287 / 262 minus 1 it won't know what to
  • do for 2015 because it doesn't have 2014
  • data so that column is just gonna appear
  • as blank click OK simple enough right
  • click and hide all right now now the
  • advantage of Tobias's version is that as
  • this pivot table shrinks our
  • 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 gonna 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 all right so the goal is to
  • build a year-over-year report in a pivot
  • table and show the percentage growth I
  • always always do that calculation
  • outside the pivot table but that has
  • issues like they get pivot data problem
  • and 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 revenue
  • as a second time change that calculation
  • the percentage change from years
  • previous item and then you have to hide
  • hit the extra column great great trick
  • from Tobias I wanna thank you for
  • stopping by we'll see you next time for
  • another net cast from MrExcel

Download File

Download the sample file here: Podcast2152.xlsm

Title Photo: 12019 / Pixabay