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.
One of my favorite feature of a pivot table is to group dates.
With this, you can instantly create insightful and ready to present reports. When you work with a data where you have dates, 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 quick and easy to do.
And in today’s post, I’d like to show exact steps to group dates in a pivot table using different basis [Year, Quarter, Month, and Time].
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 days to get a week wise summary. Please follow below steps to 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.
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.
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.
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.
Which pivot table feature you love the most?
Please share your views in the comment section, I’d love to hear from you. And, don’t forget to share this tip with your friends.
More Pivot Table Tips