This article is part of an Essential Guide, our editor-selected collection of our best articles, videos and other content on this topic. Explore more in this guide:
3. - Accounting and finance Excel tips: Read more in this section
- Use the MATCH function to find the next largest value
- How to build a year-over-year report using a pivot table
- Rank the top five customers in an Excel invoice register
Explore other sections in this guide:
- 1. - Problems with using Excel for finance
- 2. - Moving from Excel to financial management applications
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.
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.
7. Excel displays the Grouping dialog. Choose Years and Quarters. Click OK. (Note: You could substitute Months for Quarters if desired).
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).
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.
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.