Manage Learn to apply best practices and optimize your operations.

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.

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

Next Steps

See if you know the basic Excel functios for accounting

Dig Deeper on Financial analytics and reporting

Join the conversation


Send me notifications when other members comment.

Please create a username to comment.

Does anyone know if there's a way to get the % change into the pivot table itself?
Yep, you can get the % change into the pivot table itself. First drag "Revenues" to the Values section again so that it shows up twice. Click the new value (in this example, it would be "Sum of Values2" and select "Value Field Settings...". Next, click the "Show Values As" tab and from the dropdown, select "% Difference From". The last step is to select "Year" from the "Base field:" options (this tells Excel what to reference when calculating "% Difference From"), and then select "(previous)" from the "Base item:" choices. This will only be available if you've already grouped dates the way the article describes. The only downside to this approach is that because you've added a second value, you'll see a column with blank values for the first year in your date series since there's no "previous year" to compare to. So in the example above, there would be a "% Change" column to the right of 2014 with no values, though you could hide the column if you wanted to make the table look cleaner (or choose banded columns from the PivotTable Tools Design ribbon). Hope that helps.
I have multiple spreadsheets with allocations across the columns, the rows contain individual purchases. I need to get the totals for all the item headings i.e fuel, electricity telephone. I then want the totals entered into three categories being administration expenses, production expenses and sales expenses