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
Click here to download the Excel workbook (contains instructions).
Note: this is an .xlsx file. Please ensure your browser doesn’t change the file extension to a .zip file.
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
More PivotTable Tutorials
Do you want more like this?
Let me know by sharing this tutorial on Facebook, Twitter, LinkedIn or +1 on Google by clicking the icons below, or leave a comment and tell me what other tutorials you'd like.