Grouping MS Excel Data In Pivot Tables (e.g. By Month, Quarter, Year)

Slightly off topic, but this has been driving me mad, and after wasting time with formulas, calculated fields, formatting as short date, today I found a nice simple solution.

This might be useful for anyone who wants to export and group data in Microsoft Excel by date – e.g. to quickly make sense of ranking results, Google Analytics exports or Basecamp time tracking data.

Pivot tables in Microsoft Excel are super useful, and sometimes you might want to sub total the information. There are so many directions you can shuffle the information in pivot tables, but it’s not obvious how to do this simple sub total.

When you have the information you need in the Pivot table, right click over a date entry, and click ‘Group’.

Pivot table - group by date

You will then have a list of grouping options for your dates.

Group pivot table data by month, quarter, year

Choose your grouping option, and you will be presented with your data in a neat high level view.

Microsoft Excel Pivot table data grouped by month