Create Pivot Table Views by Month, Quarter, Year for Excel Reports

By Michael Alexander

The pivot tables you create in Excel for your dashboards and reports often need to be tweaked to get the look and feel you’re going for. Raw transactional data is rarely aggregated by month, quarter, or year for you. This type of data is often captured by the day. However, managers often want reports by month or quarters instead of detail by day.

Fortunately, pivot tables make it easy to group date fields into various time dimensions. Here’s how:

1Build a pivot table with Sales Date in the row area and Sales Amount in the values area, similar to the one in this figure.

You can build this pivot table to start.

2Right-click any date and select Group, as demonstrated in this figure.

The Grouping dialog box appears.

3Select the time dimensions you want. In this example, select Months, Quarters and Years.

Select the time dimensions that suit your needs.

4Click OK to apply the change.

Here are several interesting things to note about the resulting pivot table. First, notice that Quarters and Years have been added to your field list. Keep in mind that your source data hasn’t changed to include these new fields; instead, these fields are now part of your pivot table.

Another interesting thing to note is that by default, the Years and Quarters fields are automatically added next to the original date field in the pivot table layout, as shown in the figure.

5After your date field is grouped, you can use each added time grouping just as you would any other field in your pivot table.

This figure shows the newly created time groupings to show sales for each market by quarter for 2010. You can use your newly created time dimensions just like a typical pivot field.