Excel - Grouping Dates by Month in a Pivot Table

For this post I am referencing the work creating by the talented minds at Chandoo.org, a great resource for advancing your knowledge of Excel.  Here's the direct link to their pivot table post.


I ran into a problem when creating a pivot table yesterday.  Here's my raw data, a series of daily bank payments that I wanted to group to show what months the account had made payments in.

Creating the pivot is pretty easy, but the result is that for each account the payments show up by day instead of summed up by month.

Here's the trick that the guys at Chandoo.org showed me.  Right click on a date cell; the pull-down menu will show a category called "Group...". Click Group and you can select month, year, etc.

The result is a nice pivot table with your numbers summed up by month. 

One caveat, it seems when you group by month it only looks at the month part of a date field, so if you have dates spanning multiple years they will get summed up to the specific month.  So you have to select group by month and year, this will do the trick!