Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.
Worth $20, Absolutely Free
A pivot table is full of awesome features. It doesn’t matter how big your data is, there is no problem for pivot table to handle it.
Creating a group of dates in a pivot table is one those features. With this, you can instantly create insightful and ready to present reports.
When you work with a data where you have dates in it, the best thing to summarize it in months, years, or quarters.
Let’s say you want to group all the dates as months so, instead of adding a different column in your data, it’s better to grouping dates option.
It’s easy, quick and a big time saver.
And in today’s post, I’m going to show you how to group dates in pivot table using different basis [Year, Quarter, Month, and Time].
So let’s get started.
To combine dates in your pivot table please follow these simple steps.
Quick Tip: You can also use above steps to group dates in a pivot table by years, quarters and days.
You can also create a group of 7 to get a week wise summary. Please follow below steps for this.
Quick Note: You can use above steps to create a group of dates for any number of days. And, please note that week created by pivot table is not on the basis of Mon-Sun.
Check this out ⇝ Pivot Table Tricks
You also have an option to group dates by time periods. Let’s say if you have dates with time and you want to group dates to get an hourly summary.
Just follow these simple steps to do that.
Quick Tip: You can also use above steps to group dates in the pivot table by minutes and seconds.
You can also create a group of dates by using a custom range. Just follow these simple steps for this.
In 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 months but only for 6 months of 2015 and all the month of 2014 in one group.
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.
Check this out ⇝ Conditional Formatting in a Pivot Table
It happens sometimes that you need to use more than one-time span to group dates in a pivot table.
Let’s suppose in below pivot table you want to group dates by quarters and months.
Once you create more than one group for dates in the pivot table, you will also get an expanding and collapsing option.
If you want to get back your dates or want to ungroup dates you can do that with “ungroup‘ option.
Download this sample file from here to learn more.
Using this option for grouping dates in a pivot table is a time saver. You don’t have to insert those extra columns in your source data.
Want to know the best part?
You have different options to combine dates and create a summarize pivot table.
I hope this pivot table tip will help you get better at Excel.
Now tell me one thing. Do you use it in your work or not? Or you never heard about this. Please share your views in the comment section, I’d love to hear from you.
And, please don’t forget to share this tip with your friends.