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.
- Select any of the cells from the date column.
- Right-click on it and select group.
- You will get a pop-up window to group dates.
- 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.
- Select “days” option from the group by option.
- Enter 7 in a number of days.
- 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.
- Select any of the cells with a date.
- Right click on it and select group.
- Select hour from the group by option.
- 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.
- Right-click on date column and select “group”.
- Untick starting and ending date in auto option. And, enter your custom dates.
- Select month from by group option.
- 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.
- First of all, select the group option from the right menu.
- After that, select quarters and months.
- 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.
- Select a cell from the data column.
- Right-click.
- Select “Un-Group”.
Hi Puneet – I’m finding your page extremely helpful, thanks very much.
Do you have detail on how to do the above date grouping using VBA instead? I’ve used your Pivot Table in VBA page to create mine with Date as a Row, but want to group by month in the code.
Thanks.
This is great, but I have a challenge. I have dates spanning over more than 12 months and when grouping by month, it combines the details for a month the 2 years into 1 row. How is this solved?
Is this for Excel 2016? When I right click the column I need to ungroup it doesn’t have group as an option. It’s messing up my pivot tables, I select to have the date and it breaks it up by quarters. I can’t see what I need to see and I need to send out this report like yesterday. 😬
Great way to show trick and tips appreciate it very much!!!!!!!!!!!!!!!1
Hi Puneet
I loved your guidance on the Grouping Option in Pivot Tables
Very clear explanations
Neil
Thanks a lots. greate
Thanks for sharing.
My favorite part of pivot table is “show report by filter pages”.
Thanks for sharing. 🙂
Stunning Trick, Many thanks
Welcome.
nice info…..
I’m so glad you liked it. 🙂
Nice information
Thanks for your words. 🙂
Really useful information presented in a crisp way. Thank you.
Thanks Sandip for your words.