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.