It’s monday morning and you’ve got a pivot table on your hands – its source data consisting of three columns – product, date and sales volume over a preiod of time.
A pivot table created using this as underlying data may typically have the date running across rows, the product category as the column or page field and finally the sum of sales appearing in the data field in the center. In this format, the pivot table would tend to club the sales based on each day of sales. For example the first row would contain the sales for 1-Jan-2009 and the second one would have the sales for 2-Jan-2009 and so on and so forth.
While this is good, what if we wanted to aggregate the data at the month, quarter and yearly level? Normally one would then go back to the underlying source data and insert three new columns – one each for month, quarter and year. We would then go back and modify the pivot table so that these additional columns are made a part of the data set. We would then drag and drop these fields in the actual pivot table in the workbook. We then finally refresh the pivot table to get our pivot table to aggregate the data in the format that we wanted. A lot of work and that’s what one would have done if one were not to be aware of the group by feature available in a pivot table. Let’s see how it works. Right click on the Date field in the pivot table, select “Group and show detail” and then select “Group”.
In the box that appears we can select from a number of options available – the granularity starting from seconds and going all the way up to an year. In our case, we can select Days, Months, Quarters and Year as the time durations to group by.
Press OK and hey presto…our pivot table suddenly has three new columns each aggregating the data neatly into the time durations we wanted – month, quarter and year.
Keyboard Shortcuts in Excel to Sort, Add, Hide Pivot Table Items and Fields 
Slicer VBA Code – Create, Change or Modify a Pivot Table Slicer using VBA 
Slicer in Excel – The Pivot Table Remote Control 
Pivot Table Quick Tip – Using Pivot Table to Separate Data 
Hide Pivot Table Field Header – Pivot Table Quick Tip 
Pivot Table in Excel 2007 – Comparison with Excel 2003 
Really helpful. Saved me a lot of time.
January 5th, 2011 at 9:44 pmThanks, too easy to be easy to figure out and saved me a lot of coding time.
May 13th, 2011 at 8:48 amI would like to ask … why i cannot find the “Group and show detail”, and do not have the box pomp up on the excel … where i can find it? or any option i need to choose before that?!
July 12th, 2011 at 10:22 pmThanX
May 25th, 2012 at 1:11 amGreat Work SIR
Been driving me mad all afternoon. Thanks for your help 🙂
August 17th, 2012 at 10:40 amNice.. helped a lot. But in my pc months appear in Greek while I would like it to appear in English.. Tried through field setting but no luck… thx for helping
September 7th, 2012 at 5:34 amI should thank you a lot for your time and effort to help others; may Allah reward you and your team.
November 16th, 2012 at 10:40 pm@Ruby, (if you still have the problem) make sure that you’ve selected a cell in the Date column, and then right click on it, and you’ll find a box popped up like in Pic#3.
I would like to ask … why i cannot find the “Group and show detail”, and do not have the box pomp up on the excel … where i can find it? or any option i need to choose before that?!
September 24th, 2013 at 3:32 pmIt very time saving for me for managing data
December 22nd, 2014 at 2:26 amAwesome. Did not realize I could select Months AND Years.
February 2nd, 2017 at 10:04 amWith VBA, how do you create slicers to show years and months , when pivots are only Region and Managers. Manually it is easy to group, but I am interested to see with vba.
February 27th, 2017 at 10:04 am