MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Another Way to Calculate Year-Over-Year


May 02, 2019 - by Bill Jelen

Another Way to Calculate Year-Over-Year. Photo credit: Denys Nevozhai at Unsplash.com.

Instead of creating a formula outside of the pivot table, you can do this inside the pivot table.

Start from the image with column D empty. Drag Revenue a second time to the Values area.

Look in the Columns section of the Pivot Table Fields panel. You will see a tile called Values that appears below Date. Drag that tile so it is below the Date field. Your pivot table should look like this:

This pivot table has customers down the left side. Across the top are four columns: Sum of Revenue for 2021, 2022. Then Sum of Revenue2 for 2021 and 2022.

Double-click the Sum of Revenue2 heading in D4 to display the Value Field Settings dialog. Click on the tab for Show Values As.


Change the drop-down menu to % Difference From. Change the Base Field to Date. Change the Base Item to (Previous Item). Type a better name than Sum of Revenue2 - perhaps % Change. Click OK.

In the Value Field Settings dialog, choose the second tab, called Show Values As. In the top drop-down menu, choose % Difference From. The Base Field should be Date. The Base Item should be (previous).

You will have a mostly blank column D (because the pivot table can't calculate a percentage change for the first year. Right-click the D and choose Hide.

Thanks to Tobias Ljung for this method.

Title Photo: Denys Nevozhai at Unsplash.com


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.