Group Data in Pivot Table for Quarterly Report

During a recent Excel training class, one student asked me if was possible to create a Quarterly Summary Report in a Pivot Table. Yes, it is quite easy to do this – as you will see in the video.

In the Data Set that we are using for this Pivot Table we have four territories and 12 months of sales for each territory over a one year period. The Months (Jan, Feb, etc.) are not actual dates (e.g.1/1/08,) they are Labels.

In an earlier video, I showed you how easy it is to create new fields for Months, Quarters & Years from a Date field. Because our months are Labels, we will GROUP the Months to create the 1st, 2nd, 3rd, and 4th Quarters in our Pivot Table.

Here are the steps to follow in this lesson:

Create a Pivot Table using the default settings in the Pivot Table Wizard. Place the Months in the Row area, the territory in the Column area, and the Sales in the Data area.
Select the Labels January, February, March. On the Pivot Table drop-down menu select Group & Show Detail – Group.
Rename the generic label (Group1) to 1st Qtr.
Follow the same sequence of steps to create Groups for the 2nd, 3rd, and 4th Quarters.
Rename the new, generic field (Month2) Quarter.
Use the Toolbar Commands to Hide & Show Detail as desired.