How To Create A Year-Over-Year Report Using A Pivot Table

In this lesson you will create a year-over-year report using pivot table. You may need that for reports in Excel. Analysts will be especially happy because of that lesson.1. Select a cell in your data sheet.sample pivot data2. Go to Insert ribbon’s tab and click on Pivot Table.

ribbon click pivot table

3. A dialog box will appear, keep the default settings and click Ok.

dialog box create pivot

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.

PivotTable Field List

5. Move cell pointer to the first entry of column A.

entry a column

6. Click on the Group Selection from the ribbon.

ribbon group selection

7. A Grouping dialog box will appear. Select years and months (or quarters whatever you want). Then click Ok.

Grouping dialog box

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.

Row Labels Column Labels

The new look of the report will be like:

new look

9. Go to Design tab and from the Report Layout drop down list, click on Show in Tabular Form.

Show in Tabular Form

The grids will now appear.

grids appear

10. From Grand Totals drop down menu, select On For Columns Only to remove the Grand Total column appears at the right side.

On For Columns Only

 

The report will appear like:

report appear

11. Then make a heading of % Change in 2013-14 in E4 and % Change in 2014-15 in F4.

make heading

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.

type formula

13. Now go to E5 and right click then select Format Cells.

Format Cells

14. A dialog box will appear. Click on Percentage and select the Decimal Places: 1 then click Ok.

Percentage Decimal Places

15. Copy E5 down to all rows.

Copy down

16. Repeat the steps 13-15 for F5.

repeat steps

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.

Revenues

18. Then click on the Field Headers so that they will not be appearing in the final report (if you want).

Field Headers

The final year-over-year report will be like:

final report

 

Template

You can download the Template here – Download