A question that is often asked when I cover Pivot Tables in Excel courses is “Can I group the data by months?”
The answer is “yes you can”. In fact you can group by days, months, quarters and years, as well as hours and minutes!
In August 2012 I delivered a webinar about Pivot Tables. The recording of the 1-hour webinar is available for purchase from here. Below is a short (less than 3 minutes) clip, extracted from the video, in which I demonstrate how to group data in a Pivot Table by month and by day.
I used Excel 2011 for Mac, however, the information and demonstration is equally valid for Excel for Windows
The supporting documentation can be downloaded here
Since it isn’t obvious from Excel’s UI, it might be helpful to note that by holding down CMD on Mac (CTRL on Win) you are able to select more than one grouping value in the “By” list box.
This is essential if you want to group by Year and then by Month, for example. If the date range spans more than one year and is grouped by Month *only*, Excel will add the months together across years. Here’s a visual comparison of the two:
## Group by Year AND Month:
2012
Jan
Feb
…
2013
Jan
Feb
…
## Group by Month ONLY:
Jan
Feb
…
(No years will be shown and a maximum of 12 months will be displayed.)