Excel 2024: Create a Year-over-Year Report in a Pivot Table
May 6, 2024 - by Bill Jelen
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.
