Easy Year-over-Year

Learn Excel from MrExcel podcast, episode 1998. Pivot Table Year over Year.

I'll be podcasting this entire book, go, click that “i” on the top right hand corner, for the MrExcel Excel playlist.

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Today the first topic in the book about Pivot Tables and how to create a year-over-year report. So we're starting out with the data set, that spans two years, you see, we go from 2018 through 2019. I'm going to create a Pivot Table, Insert, PivotTable, OK, that’s okay, let’s it go to a new worksheet.

On the new worksheet we're going to take the Date field and drag it to the Rows area. And then, you see, in Excel 2016 it automatically groups things up. Now I know most people are still in Excel 2013, maybe Excel 2010, so I'm going to press Ctrl+Z to undo. That's the experience you'll get in 2010 and 2013, we're going to take the Revenue field and drag it to the Values area. So we start out with an ugly report, there's going to be hundreds and hundreds of rows, because it's every single day that we had sales. But I'm going to go to the very first date field, very first date field, go to the PivotTable Tools, Analyze, in 2013 or 2016 and 2010 it was called Options, and choose Group Field. Make sure that you choose a date before you choose Group Field and roll things up to Months and Years. Or Quarters and Years, or Months and Quarters and Years, click OK. And what we get, is a new virtual field called Years down here in the Rows area.

Now some annoyances. It's annoying that they don't give us Year totals, it's easy enough to fix that, although in my case I don't need to fix that. We click Field Settings and Automatic, because I'm not planning on leaving the Years here. Of course every time I create a PivotTable, I go to Design and say Show in Tabular Form to get everything into its own column. And then Repeat All Item Labels to fill in those blanks along the left hand side. Again, that wouldn't be necessary here, because I'm not planning on leaving the Years where it is, I'm going to take the Years and drag it over to the Columns, and that's the report that I was shooting for.

Alright, so we have Months going down the left-hand side, 2018 to 2019. In column D, what do I really want, I don't want a Grand Total, I'm going to right-click and say Remove the Grand Total and now we're outside of a PivotTable. So, %Change, very tempting to build this formula using the mouse or the arrow keys, but you can't, because you'll get the… get Pivot Data function, which does not easily copy. So =C5/B5-1, we’ll format that as a percentage and double click to copy it down, right. Beautiful report showing where we were last year, where we were this year and then the percentage change from year to year.

Well, this tip and 40 others, are all in this book. Go ahead, click that “i” on the top right hand corner, by the book. Alright, so you start off with the data set with multiple years, do Insert, Pivot table, drag the date field to the row area. And in Excel 2016 press Ctrl+Z to undo their automatic grouping updates. Why don't we just leave this here? Well, it actually depends. If you would have data from, let's say, January 2nd through December 30th, they're not going to group that data correctly. It has to spend an entire year for you to get the year field. It might be that it works out for you, I'm a bit of a control freak, I will rather just have control, that's why I undo. There is: drag the revenue to the values area, select any cell, any date in the row area and then Group Field, choose Months & Years, drag years over the column area, right-click the Grand Total to remove and then build a variance, type in the formula, don't use the mouse or arrow keys.

I have no idea how to pronounce it, but thanks to this reader (Александр Воробьев) for suggesting this tip and thanks to you for stopping by. We'll see you next time for another netcast from MrExcel.