Grouping Dates is very easy with a Pivot Table. All you have to do is Right Click on your Date values (which are either in the Row or Column Labels of your Pivot Table), then choose the Group option. From the dialogue box you can choose to Group by Days, Months, Quarters or Years.
STEP 1: Right click on your Date values and choose Group.
STEP 2: Select the combination of grouping you prefer. Click OK.
Your pivot table is now grouped by dates!
Helpful Resource:
Hi John
Not all financial years start in January and end in December. Ours starts in July and ends in June. How do you get Q1 to be Jun to Aug etc. Also when comparing financial years you want from Jul to Jun not Jan to Dec
Thanks
Hey Susan,
This can be achieved using a helper column in your data source.
Have a look at this video that is from my Xtreme Pivot Table online course: Group by a fiscal year and quarter
Cheers,
John
Hi John! Sometimes there is an error with group. Why sometimes I couldn´´´t group in pivot tables? I don´t´t know
Hello Hava,
That is because your values are in text. As this is the case, any text to be grouped need at least 2 cell selections highlighted then you can Right Click and Group.
To fix your values that are in text, have a look at this tutorial: https://www.myexcelonline.com/turn-text-to-values-with-excel-paste-special-values/
I hope this help 🙂
John
Hava,
I would like to add a little more information about trying to Group a DATE column in a pivot table.
The ENTIRE column from your data source has to contain dates. This means you can’t group the date column if even one cell in the data column contains:
* Text
* Error Message
* Blank Cell
Many time it means updating the cell to a date, reducing your range to not include the cells that aren’t dates, or updating the cell with a bogus date so you can later filter out that information.
