Excel 2024: Create a Year-over-Year Report in a Pivot Table

Let's say you have two years worth of detail records. Each record has a daily date. When you build a pivot table from this report, you will have hundreds of rows of daily dates in the pivot table. This is not much of a summary.

Choose one of those date cells in the pivot table. From the Analyze tab in the Ribbon, choose Group Field.

Because you are on a date field, you get this version of the Grouping dialog. In it, deselect Months and select Years. The daily dates are rolled up to years. Move the Years field from Rows to Columns.

Instead of a grand total in column D, you probably want a percentage variance. To get rid of the Grand Total column, right-click on the Grand Total heading and choose Remove Grand Total.)

To build the variance column as shown below, you need to write a formula outside the pivot table that points inside the pivot table. Do not touch the mouse or arrow keys while building the formula, or the often-annoying GETPIVOTDATA function will appear. Instead, simply type =C5/B5-1 and press Enter.

Bonus Tip: Another Way to Calculate Year-Over-Year

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

Start from the image above and clear column D. 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:

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.

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.