In this second tutorial, in my 3 part series on PivotTable tips and tricks, I’m going to show you how to group data like dates into years, quarters or months, and ages into groups like this PivotTable below:
Watch the Video
For best viewing watch the video in 720p HD full screen.
Download the Workbook
Enter your email address below to download the sample workbook.
Written Instructions - How to Group Dates
1. Ensure your data is in a date format, i.e. numbers not text.
2. Create your PivotTable and right click on any date in the PivotTable > Group
3. Select the groupings from the 'By' list:
4. Click OK
If you get an error it's most likely because your source data isn't formatted as a date.
You can use Text to Columns to fix this.
Written Instructions - How to Group Ages into Bands
1. Create your PivotTable and right click on any number in the age group column header of the PivotTable > Group
2. Edit your start and end ages or leave as Auto.
3. Change the 'By:' number to suit your needs:
4. Click OK
![Excel PivotTable Add Percentage of Total Column [Video]](https://www.myonlinetraininghub.com/wp-content/uploads/2013/04/pivot_tip_percent_total_thumb-71x80.png)
Excel PivotTable Add Percentage of Total Column [Video]![Excel PivotTable Month Comparison [Video]](https://www.myonlinetraininghub.com/wp-content/uploads/2013/04/pivot_tip_mth_change_thumb-65x80.png)
Maxine Brown
Hi Mynda,
I want thank you for sharing the may ways of utilizing Microsoft Excel. I am particularly interested in Pivot tables. Exel recognizes Quarters beginning in January of a year. In my location, the Quarter begins in April. How do I use Excel to recognize Quarter 1 beginning in April?
Mynda Treacy
Hi Maxine,
You need to add a column(s) to your source data that classifies the dates into their fiscal periods. Then you can use that field(s) in your PivotTable and Slicers.
I hope that points you in the right direction. If you get stuck, please post your question on our Excel forum.
Mynda
Tracy
Hi Mynda,
Now I realise that grouping the dates is not what I’m after, I need to see my list of dates but have a subtotal by month. When I select sub-total I get a total for each date and not a total of all the spends in June and July.
Is there a way around this?
tnx
Mynda Treacy
Hi Tracy,
Please post your question on our Excel forum where you can upload a sample Excel file with your data and a mock up of your desired solution so we can see what you want and help you further.
Mynda
Tracy
HI Mynda,
I have fixed it – I added a total row at the end of my data which created a blank cell in the dates column. Removed the row and now all happy days.
tnx
Tracy
Hi Mynda,
My source data is definitely in date format – I get sum, average in the bar below. However, when I click on a date and select group, I get “cannot group that selection”. Any ideas?
tnx
Nicci
Thanks for the above video Mynda! Bless you! 🙂
Mynda Treacy
You’re welcome, Nicci. Glad we could help.
Suman Dutta
Thank you for your vedio of pivot table presentation. your sound is clear but vedio charecter too small so kindly guide to me how to display screen in Big charecter.
Thanks again for presentation. Its help for me.
Kind regards
Suman
Mynda Treacy
Hi Suman,
You can watch the video in full screen. Simply start playing the video and then click on the ‘Full Screen’ icon in the bottom right hand corner of the video screen.
Kind regards,
Mynda.