How to Show Year over Year Growth

In this Excel charting tutorial lesson, you will create a year-over-year report using a pivot table. You may need that for reports in Excel. Analysts will be especially happy because of that lesson.

Year over year data analysis

1. Select a cell in your data sheet.

sample pivot data

2. 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 columns A and B.

PivotTable Field List

5. Move the cell pointer to the first entry in 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 months 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 this:

new look

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

Show in Tabular Form

The gridlines will now appear.

grids appear

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

On For Columns Only

How to show year over year % growth?

The report will appear like this:

See also  Search for string in column

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 appear in the final report (if you want).

Field Headers

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

final report

You now have a pivot table that shows the year over year growth in Excel. You can further customize the pivot table by adding a chart, changing the summary calculations, or filtering the data to show only specific years.