MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

May 01, 2019 - by Bill Jelen

Create a Year-over-Year Report in a Pivot Table. Photo Credit: Henry & Co at

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.

A pivot table is reporting daily dates. Choose one date cell in the pivot table. Click the Group Field icon in the Analyze tab of the Ribbon.

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.

In the Grouping dialog box, you can choose Seconds, Minutes, Hours, Days, Months, Quarters, and/or Years. In this image, only Years is selected.

The daily dates are rolled up to years. Move the Date field from Rows to Columns.

Drag the date field from Rows to Columns in the Pivot Table Fields pane.

The result is almost perfect. But 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.

The resulting pivot table has customers in column A, and then 2021 in column B and 2022 in column C. A calculation out side the pivot table in column D gives you the % Change with the formula of =C5/B5-1.

Title Photo: Henry & Co at

Bill Jelen is the author / co-author of
Excel Insights – A Microsoft MVP Guide to the Best Parts of Excel

There are fewer than 100 Excel MVPs worldwide. 24 of them have contributed to this book. Written, edited, reviewed and printed by Excel MVPs, this is practical Excel passion undiluted, with each MVP highlighting some of their favorite topics.