How To Create A Year-Over-Year Report Using A Pivot Table
2. Go to Insert ribbon’s tab and click on Pivot Table.
3. A dialog box will appear, keep the default settings and click Ok.
4. A new blank worksheet will be created, containing a PivotTable Field List. Select your desired fields, that is Date and Revenue (for this example) that will appear in column A and B.
5. Move cell pointer to the first entry of column A.
6. Click on the Group Selection from the ribbon.
7. A Grouping dialog box will appear. Select years and months (or quarters whatever you want). Then click Ok.
8. The pivot table will illustrate the years and month stacked in an outline view. Drag the Quarters tile from the Row Labels to Column Labels from the bottom of the PivotTable Field List.
The new look of the report will be like:
9. Go to Design tab and from the Report Layout drop down list, click on Show in Tabular Form.
The grids will now appear.
10. From Grand Totals drop down menu, select On For Columns Only to remove the Grand Total column appears at the right side.
The report will appear like:
11. Then make a heading of % Change in 2013-14 in E4 and % Change in 2014-15 in F4.
12. In E5, type a formula =ABS(C5/B5-1) and in F5, type a formula =ABS(D5/BC5-1). Do not use the mouse or arrow keys to select any cell.
13. Now go to E5 and right click then select Format Cells.
14. A dialog box will appear. Click on Percentage and select the Decimal Places: 1 then click Ok.
15. Copy E5 down to all rows.
16. Repeat the steps 13-15 for F5.
17. Select the Sum of Revenues heading and go to the Options menu. Click in the Active Field box and write Revenues followed by a space.
18. Then click on the Field Headers so that they will not be appearing in the final report (if you want).
The final year-over-year report will be like:
Template
Further reading: Basic concepts Getting started with Excel Cell References
