Excel: Create a Year-Over-Year Report

Problem: I have two years of data by daily dates. I would like to see year-over-year sales by month.

Strategy: Amazingly, it takes only 10 mouse clicks to create this report. Follow these steps:

1. Select one cell in your data set.
2. Insert, Pivot Table, OK.
3. In the PivotTable Field List, choose Date and Revenue.
4. At this point, the Row Labels heading is selected. Move down one cell so that the cell pointer is on a date.
5. Choose Group Field. Months is already selected. Add Years. Click OK.
6. In the PivotTable Field List, drag Years from the Row Labels drop zone to the Column Labels drop zone. You will now have this report.

1. Pivot years to go across the report.
   7. On the Design tab, open the Grand Totals dropdown and choose On For Columns Only which is the very strange way to delete the grand totals along the right side of the report.
   8. In D4, type % Growth.
   9. In D5, type =D5/C5-1. Do not use the mouse or arrow keys while entering this formula!
   10. Format D5 as a percentage with 1 decimal place.
   11. Copy D5 down to all rows.
   12. Select the Sum of Revenue heading. In the Options ribbon tab, click in the Active Field box and change the field name to "œRevenue "œ (with a space after the word Revenue.)
   13. On the Options ribbon tab, click the Field Headers to prevent those from being shown in the report.

   

2. Year-over-year report created with a pivot table.

   Gotcha: If you used the mouse in step 9, you will find that the percentage growth does not change as you copy it down. Go back and re-enter the formula, or follow the steps in "œCalculations Outside of Pivot Tables" on page 408.