Essential Guide

Ultimate Excel finance guide

A comprehensive collection of articles, videos and more, hand-picked by our editors
Q

Creating a year-over-year report using an Excel pivot table

Microsoft Excel consultant Bill Jelen, AKA "MrExcel," shows how to use a pivot table feature many Excel users aren't aware of.

FROM THE ESSENTIAL GUIDE:

Ultimate Excel finance guide

+ Show More

An Excel pivot table is a great tool for summarizing data, and a little-known feature lets you roll daily dates up to months, quarters and/or years.

In Figure 1, two years' worth of detail records have a Date field that is showing daily dates. 

Create PivotTableFigure 1

To create a year-over-year report, follow these steps:

1. Select one cell in the data set.

2. From the Insert menu, select PivotTable.

3. In -the Create PivotTable dialog, accept the defaults shown above in Figure 1. Click OK to create a new blank worksheet to hold the pivot table.

4. You will see a PivotTable Field List. Add a checkmark next to Date and Revenue. The daily dates should appear down the left side of the report with revenue for each date in column B.

5. Move the active cell to one of the dates in column A.

6. From the ribbon, select Group Field as shown in Figure 2.

Excel group fieldFigure 2

7. Excel displays the Grouping dialog. Choose Years and Quarters. Click OK. (Note: You could substitute Months for Quarters if desired).

Grouping dialog boxFigure 3

Initially, the pivot table will show the years and quarters stacked in an outline view as shown in Figure 4.

8. To fix this, drag the Years tile from the Row Labels to Column Labels in the bottom of the PivotTable Field List. (See the red arrow in Figure 4).

Row Labels to Column LabelsFigure 4

You will now have one quarter per row, with columns showing last year, this year and a grand total. To finish the pivot table, follow these steps:

9. On the Design tab in the ribbon, open the Report Layout drop-down and choose Tabular Form.

10. Open the Grand Totals drop-down and choose On For Columns Only. This removes the column that totals the two years.

11. Add a heading to D4 with % Change.

12. In D5, type a formula of =C5/B5-1. Be careful not to use the mouse or arrow keys to select C5 or B5. When you use the mouse or arrow keys, Excel will insert GETPIVOTDATA functions that use absolute references and will not copy down the column.

13. Copy D5 down to D6:D10.

The final report should appear as shown in Figure 5.

Excel year-over-year reportFigure 5

About the author:
Bill Jelen, AKA "MrExcel," has been a Microsoft Excel consultant for over twenty-five years. Read more of his expert Excel tips and tricks at his website, MrExcel.com

This was first published in October 2013

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Essential Guide

Ultimate Excel finance guide

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

3 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchManufacturingERP

SearchSAP

SearchOracle

SearchBusinessAnalytics

SearchContentManagement

SearchDataManagement

SearchCRM

Close