Learn Excel, Pivot Tables & Charts

Grouping Dates in Pivot Tables

Last updated on November 16, 2009

Chandoo

Do you know you can group dates in pivot tables to show the report by week, month or quarter? I have learned this trick while doing analysis on a pivot table today. In this online lesson on pivot tables, I will teach you how to group dates in pivot tables to analyze the data by month, week, quarter or hour of day.

Let us say you have a sales transaction database like this:

To show “Total units sold by salesperson in each month” in pivot table

First make a pivot table [video tutorial] with Sales person as column header, Transaction date as row header and “total units” as pivot values.
Now select any transaction date, right click and select “Group”
In the group dialog box, select “Months” for group by option. Press ok.
Presto! your data is grouped by month. No more than 3 seconds and you are ready analyze the data by month and find trends.

I have made a small screen-cast to show how this can be done. See it here:

Special cases for grouping dates in pivot tables

While the above example is simple, there are various things you can do when you are grouping data in pivot reports. Here are some special cases and how to get the grouping in pivot tables.

Group by Quarter & Month:

To group the pivot tables by Quarter and Month,

Select “group” option.
Select both “month” and “quarter” in the “group by” option, Click ok.

Group by Week:

To group the pivot table dates by week,

Select “group” option
Select “day” in the “group by” option. When you do this, the “number of days” box will be enabled. Enter the number of days as “7” to group your pivot report by week.

Group by Hour of the day:

Assuming your data has dates along with the actual transaction time, you might want to analyze the sales by hour of day, to find out say “if one product is selling more than other during certain hours”.

To group the pivot table dates by hour of the day:

Select “group” option
Select “hour” in the “group by” option.

Collapsing & Expanding Pivot Table Groups:

When you group pivot reports by more than one “group by” you will see a little icon with + or – sign to expand or collapse the groups. Using this feature, you can easily deep dive in to a particular group to do further analysis. For eg. you can collapse all quarters and just expand Q2 to understand why the sales went up.

Another useful feature of these collapse / expand buttons in pivot tables is that, when you make a pivot chart, the collapsed groups are collapsed in the pivot chart too. And it is dynamic, ie, if you expand a group in the pivot table, the chart gets updated and shows more details.

Tell us how you use the group / un-group feature in pivot tables?

I am finding numerous possibilities with the group / un-group feature of pivot tables. I will learn new things and share them with you as we go along. Meanwhile, share your tips, experience and ideas using comments. I would love to learn from you.