Excel Magic Trick 1267: PivotTable Automatic Date Grouping: New in Excel 2016

About this tutorial:

Video duration: 3:20
Download File:
See that in Excel 2016, a Date Field from a Table that is dropped into the Row Area of the PivotTable automatically groups into Years, Quarters and Months. See how to create a Monthly Revenue Report.

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

21 thoughts on “Excel Magic Trick 1267: PivotTable Automatic Date Grouping: New in Excel 2016

    Reinier Wolters

    (May 22, 2019 - 1:49 pm)

    It works for me, but for my type of report want the date to be shown in the filter section of the pivot, not in the row, how can I break it down by year?

    Mason Knight

    (May 22, 2019 - 1:49 pm)

    very helpful!

    Luis Colachagua Canchan

    (May 22, 2019 - 1:49 pm)

    Muchas gracias 🙂

    Kent Lewandowski

    (May 22, 2019 - 1:49 pm)

    Thank you. Microsoft dumped this "feature" on us without explaining how to "undo" it!

    lokdeep althi

    (May 22, 2019 - 1:49 pm)

    When i am dragging the date its nit coming whats your date format

    Andrew Mesplay

    (May 22, 2019 - 1:49 pm)

    I follow the step exactly as shown above. When I drag the Date field to the Rows section, the Years/Months/Quarters does not show. Is there a setting within Excel 2016 to make this work? I know that this can be done, I think my settings are off.


    (May 22, 2019 - 1:49 pm)

    Get Message: When trying to ungroup dates for quarter and then group by months. A pivot table report can not overlap another pivot table report. There are no other pivot tables on sheet or workbook. In Microsoft help they mention about hidden tables.


    (May 22, 2019 - 1:49 pm)

    thanks helped me


    (May 22, 2019 - 1:49 pm)

    what about fiscal weeks

    Melanie Ayam

    (May 22, 2019 - 1:49 pm)

    Thank you! You saved me 🙂

    Charles BParent

    (May 22, 2019 - 1:49 pm)

    Can we group dates by weeks like in the earlier excel versions?


    (May 22, 2019 - 1:49 pm)

    OMG I can finally start growing my hair again, such a simple answer yet so bloody hard to find, you've saved me an early death with the ungroup hint 😉

    Ann Roehm

    (May 22, 2019 - 1:49 pm)

    Thanks, I looked all over the place for ungrouping the dates. Excel Help was useless.

    John Bates

    (May 22, 2019 - 1:49 pm)

    Hi thanks for this. I use pivot tables with an excel database of typically 25,000 rows and about 15 data columns. I typically look at relationships between two columns and the tables and graphs are generated manually, which is time consuming. I would really like to be able to a) predefine the relationships for the tables and graphs b) sort the tables in a specific order and calculate % c) export the tables and associated graphs into MS word in a predefined order. Is this possible or are you aware of any add on or software that allow this? Thanks


    (May 22, 2019 - 1:49 pm)

    Didn't work for me. Date did not give group options.

    Anil Dutt Bhargava

    (May 22, 2019 - 1:49 pm)

    Very nice trick, i try sorting first Qtr is Apr to June, Qtr 2 July to Sept … but manual sorting is not working, please help me

    Kasi Viswanath

    (May 22, 2019 - 1:49 pm)

    I learnt so much over the years from your videos. Thanks a lot and God bless you

    Nishant Ghosh

    (May 22, 2019 - 1:49 pm)

    When will you do next duel podcast with Mr. Excel. Its very much fun to see both of you share tricks at the same time. Please do a duel podcast

    Bill Szysz

    (May 22, 2019 - 1:49 pm)

    Very cool feature of excel 2016 !


    (May 22, 2019 - 1:49 pm)

    Among the Excel 2016 new features this one it is on the podium !

Leave a Reply

Your email address will not be published. Required fields are marked *