Categorized | pivot table

Pivot Table Quick Tip – Group By Year, Quarter, Month and Date Field

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.


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. abet alih wrote:

    Really helpful. Saved me a lot of time.

  2. Tim wrote:

    Thanks, too easy to be easy to figure out and saved me a lot of coding time.

  3. Ruby wrote:

    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?!

  4. Asif wrote:

    Great Work SIR

  5. Dawn wrote:

    Been driving me mad all afternoon. Thanks for your help 🙂

  6. Nickos wrote:

    Nice.. 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

  7. Matur Nuwun wrote:

    I should thank you a lot for your time and effort to help others; may Allah reward you and your team.
    @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.

  8. Tony wrote:

    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?!

  9. Sumit wrote:

    It very time saving for me for managing data

  10. Eric B wrote:

    Awesome. Did not realize I could select Months AND Years.

  11. Malik wrote:

    With 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.


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel