Hello everyone! Did you know…that Pivot Table date grouping is being increasingly used in Microsoft Excel?
Sometimes Pivot Table date grouping is automatic
Such grouping in the most recent version of Microsoft Excel is automatic when you have a lot of dates, so presumable it must be very advantageous for your typical Microsoft Excel user.
From basic dates for days you can group your related data into months, quarters and / or years.
Groups become Fields
These act like new fields which you can move into the Rows, Columns or Filters Areas of your Pivot Table.
Focus in with Timelines
Don’t forget on a related topic that nowadays with Microsoft Excel it’s good to use Timelines with your Pivot Tables so that in a nice spreadsheet interaction you can filter in or focus on a particular time period in your data analysis.
The now and the future
Microsoft Excel is constantly changing, constantly improving!
My example file
< as usual it’s available to download towards the end of the post >
Initial set up
Today I just copied in two columns. One with various dates for 3 years and the other with the corresponding sales amounts.
Pivot Table, a.k.a. summary table
At Pivot Table set up time, firstly, I clicked Sales Amount to add it to the Values area. But then it was very interesting to see that the minute I clicked Date in the PivotTable Fields pane it got added to the Rows area but this time it also immediately switched Date to month accumulation and automatically generated the extra fields of Quarters and Years in the Rows area. Nice!
I then moved Date (Month) to the Columns area and so my sales amounts got analysed by year and month over 3 rows and 12 columns.
Worth taking note of this
I think it would be the wrong move to take Years out of the Pivot Table set up area because what would happen is, as in my example file, for any month such as December, all December figures for the three years would just sum into the one December figure and that would make no sense at all in our analysis. So keep the Years!
The command path for this is:
PivotTable Tools / Analyze / Filter / Insert Timeline
I clicked the dropdown within the Timeline and selected Years. Then on the Timeline itself I just selected 2014. So the other two years get filtered out and I am just now left with the 2014 analysis.