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 Unsplash.com

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 Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.