How to Group Dates in a Pivot Table

By grouping dates in a pivot table, you can create instant reports.

Here’s the point: Let’s say you want to group all the dates as months instead of adding a different column in your data, it’s better to group dates.

It’s super easy. Now here’s the good news: Apart from months, you can use years, quarters, time, and even a custom date range for grouping. And today in this post, I’d like to show you the exact steps for this.

Pivot Tables are one of the INTERMEDIATE EXCEL SKILLS.

Groups Dates in a Pivot Table by Month

Below are the steps you need to follow to group dates in a pivot table.

1. Select any of the cells from the date column.
2. Right-click on it and select group.
3. You will get a pop-up window to group dates.
4. Select “Month” in the group by option and then click OK.

You can also use the above steps to group dates in a pivot table by years, quarters, and days.

Weekly Summary

You can also create a group of 7 days to get a week-wise summary. Please follow the below steps to this.

1. Select “days” option from the group by option.
2. Enter 7 in a number of days.
   
3. Click OK.

You can use the above steps to create a group of dates for any number of days and please note that the week created by the pivot table is not on the basis of Mon-Sun.

Hourly Summary

You also have an option to group dates by time period. Let’s say if you have dates with time and you want to get an hourly summary. Just follow these simple steps for this.

1. Select any of the cells with a date.
2. Right click on it and select group.
3. Select hour from the group by option.
   
4. Click OK.
   

You can also use above steps to group dates in the pivot table by minutes and seconds.

Custom Date Range Summary

In the below pivot table, you have dates ranging from 01-Oct-2014 to 31-Jun-2015.

And you want to create a group of dates by month, but only for 6 months of 2015 and all the months of 2014 in one group. Well, you can also create a group of dates by using a custom range, follow these simple steps for this.

1. Right-click on date column and select “group”.
2. Untick starting and ending date in auto option. And, enter your custom dates.
3. Select month from by group option.
   
4. In the end, click OK.
   

In above pivot, now you have dates in a group by months for the date range you have mentioned and rest of the dates which are not in the date range are in a single category.

Related: Excel Slicer

Group Two Different Fields

It happens sometimes that you need to use more than one time span to group dates in a pivot table. Let’s suppose, in the below pivot table, you want to group dates by quarters and months.

1. First of all, select the group option from the right menu.
2. After that, select quarters and months.
3. In the end, click OK.

Once you create more than one group for dates in the pivot table, you will also get an expanding and collapsing option.

Un-Grouping

If you want to get back your dates or want to ungroup dates you can do that with the “ungroup‘ option.

1. Select a cell from the data column.
2. Right-click.
3. Select “Un-Group”.

Sample File

download