Bottom line: Learn the differences between the Group Dates feature in a pivot table and grouping dates in the source data by adding extra columns for year, month, quarter, etc.
Skill level: Intermediate
One of the many reasons that pivot tables are awesome is that they allow us to quickly group dates to create summary reports. Â What does grouping dates mean? Â Well, it just means that we are grouping all the date values in a column into a year, quarter, month, day, etc.
If we have transaction level data with a column that contains the transaction date, then we might want to group the dates to report on the data by year, quarter, or month.
There are three ways to go about the date grouping in Excel:
- We can use the Group Field feature of the pivot table.
- We can create additional columns in the source data with text or numeric values using formulas.
- We can create relationships between tables with lookup formulas or Power Pivot.
Is one better than the other? Â Not necessarily. Â Like everything in Excel, there are always a few different ways to accomplish a task. Â The method you use here depends on your needs, and your company's fiscal calendar.
#1 – The Pro & Cons of the Group Field Feature
The Group Field feature allows us to quickly group a field of dates into Year, Quarters, Months, etc.
This means that the source data does NOT need to contain columns with the year, quarter or month name.  The Group Field feature will automatically create the groupings for us and summarize the data in each group.
After we create the groups on the Grouping menu, new fields are added to the pivot table field list.
How to Group & Ungroup Fields
Here is a quick guide on how to group fields in your pivot tables.
- Select a cell in the Rows or Columns area of the pivot table that contains the field you want to group. This is usually a date field, but can also be numbers.
- Select the Analyze/Options tab in the Ribbon.
- Click the Group Field button. Check out this article if the Group Field Button is Disabled.
- Select the items that you want to group the field by. For date fields this is Years, Quarters, Months, Days, Hours, Minutes, Seconds.
- Click OK.
The Field will be grouped and new fields will be added to the fields list for the groups.
To Ungroup the field:
- Select a cell in the pivot table for the grouped field.
- Select the Analyze/Options tab in the Ribbon.
- Press the Ungroup button (located above the Group button).
Automatic Date/Time Grouping Option
In Excel 2016 this grouping happens automatically when you add a date field to one of the areas in the pivot table. This is a setting that can now be turned on or off in the Excel Options menu.
Here's how to disable automatic date grouping for pivot tables:
- Go to File > Options in Excel to open the Excel Options Window.
- Click the Data tab in the left sidebar. If you are using an older version of Excel this is on the Advanced tab.
- Check the “Disable automatic grouping of Date/Time columns in PivotTables” checkbox.
- Click OK.
Here is a screenshot of the setting in the Excel Options window.
If you are on an older version of Excel 2016 then this setting is on the Advanced tab of Excel options. Here is a screenshot.
If you disable the automatic grouping feature you can still use the Group feature for pivot tables. The groups will NOT be created automatically when you drag a field to an area in the pivot table, but you can click the Group button on the Analyze tab to bring up the Group Field menu.
Where are the group fields stored?
Even though we see the new Date/Time group fields in the field list, these fields are NOT created in the source data. Â They are actually stored in the pivot cache in the background.
This can be good or bad.
Pivot tables can share a pivot cache if they share the same source data range or table. Â That means that all pivot tables that share the cache, will also share the groupings.
So, if you group a date field in one pivot table, you might get unexpected results in another pivot table. Â Your other pivot tables that share that same pivot cache could also change. Â This can cause frustration and confusion.
Unfortunately, there is no way to see which pivot tables share the same cache in the Excel application. Â We can use VBA for this.
You can unshare the cache by changing the source data range, but this can get messy if you have a lot of pivot tables. Â My PivotPal Add-in also has a feature that shows a list of all the pivot tables in the workbook and the associated pivot cache.
So the main advantage of using the pivot table group field feature is that it is quick and easy.  If you just need a quick pivot table report by year, quarter, or month, then this is an easy solution.  New features in pivot charts also allow us to drill down/up (expand/collapse) on the grouped date fields.  We can quickly go from an annual trend, to quarterly, to monthly in a chart with the click of a button.
#2 – The Pros & Cons of Grouping Dates in the Source Data
The other option is to add columns to the source data that calculate the groups. Â I explained this in more detail in my article on grouping times in Excel.
We can use the TEXT function or some of the date functions (YEAR, MONTH, DAY) to calculate the text or numeric value that represents the grouping.
This means that the grouped fields will actually be columns in the source data, and just stored in the pivot cache.
Why is this important? Â Well it gives us more flexibility to make changes to our groups. Â If you use the grouping feature of the pivot table, then ungrouping or adding new groupings can cause unexpected results to other pivot tables that share the cache.
If we add a new date grouping to the source data of the pivot, then the pivot tables that use that source data won't necessarily be impacted or changed.
Another advantage of grouping dates in the source data is when your company reports on a fiscal year that is different from the calendar year.  In this case we might need to use a formula to calculate quarters or fiscal months.  We can add this formula as a new column in the source data, then add that field to the pivot table.
The group fields feature in the pivot table does NOTÂ calculate fiscal calendars.
Another advantage is that we might want to reuse the date group columns in other summary reports, formulas, or analysis outside of the pivot table.
One big disadvantage here is sorting.  The pivot table does not always recognize the proper order of your fiscal months, and we might need to append the fiscal month number to the front of the month name.
#3 – What about Power Pivot?
Power Pivot helps with this sorting and grouping issue through the use of calendar tables. Â Calendar tables are tables that contain a list of dates for the entire time period of our data. Â They also contain additional columns for the date groupings.
We can then use Power Pivot to create relationships between our data set(s) and the calendar table to group the dates. Â This is similar to a VLOOKUP approach in Excel, and a join in database terms.
The calendar table approach is not adding any columns to the original source data table, and it is not applying the grouping in the pivot cache. Â Instead, it uses the relationships (join) created in Power Pivot to perform the grouping on the fly.
I consider this to be a bit of a hybrid approach that is more inline with grouping dates in the source data. Â However, Power Pivot has a Sort by Column feature that allows you to sort your calendar table for fiscal quarters, months, weeks, etc.
We can also create calendar tables using Power Query.  And then use lookup formulas to pull the field groupings into our source data.  Or use the merge feature of Power Query to do the lookups. Checkout my article on the The Calendar Table Explained for more details.
Here is article by my friend Ken Puls on how to create a dynamic calendar table in Power Query.
Date Grouping Method Comparison List
Wow!  That is a lot of options! And a bit of a wrinkled shirt.  Let's iron it out in an organized list… 🙂
Pivot Table Group Field Feature
- Pro – Quick and easy to create date groupings with the built-in Grouping menu.
- Pro/Con – Pivot tables that share the same cache will also share the same groupings.
- Pro – Drill up/down (collapse/expand) date group fields in Pivot Charts with buttons.
- Con – Excel 2016 automatically groups date fields, so they need to be ungrouped manually if you don't want the grouping.
Add Date Columns to the Source Data
- Pro – Date columns are added to the source data and can be used in other analysis outside a pivot table.
- Con – Source data contains more columns and adds to file. Â Can be inefficient for large data sets.
- Pro –Â Can calculate fiscal quarters, months, weeks, for fiscal calendars that don't start on January 1st.
- Con – Sorting the fiscal time periods can be challenging and require extra numbers in the text based dates.
Power Pivot Date Tables
- Pro – Date groupings created through relationships makes reporting more efficient and flexible by keeping tables narrow.
- Pro – One date table can be reused with multiple data sets, and used to create relationships between multiple (fact) data tables.
- Pro/Con – Requires creating a separate date table. Â Power Query can help make this easy.
- Con – Requires using Power Pivot. Â All users will have to have Power Pivot installed to modify or interact with the pivot tables.
What did I miss?
I don't believe there is any right or wrong answer here. Â The method you choose will depend on your needs and process.
I am sure you have some other reasons for how and why you group dates for pivot tables.  Please leave a comment below with any suggestions or questions.  I am eager to learn your methods and reasons too! 🙂  Thanks!
Thanks Jon this is a really interesting article.
I do work for two companies, one using calendar year and the other fiscal year.
I like to use pivot tables and slicers for my dashboard reports, so use a combination of pivot table date grouping and manipulation of dates in my source data for fiscal year.
i had trouble getting my month slicers in fiscal month order, in our case Jul,Aug,Sep etc but now use custom list to sort my fiscal months with some vba on workbook opening.
I find dates and reporting is a topic in it’s own right.
Regards
John
Thanks John! Using custom lists is another good tip I forgot to mention. You are right that this topic could be an entire book. This article turned out to be a lot longer than I originally anticipated as I started to think about all the different ways we can group dates, especially for fiscal calendars.
Hello Jon,
there is another con with pivot tables and grouping, you can not group if you have blanks. Consider grouping a column with delivery dates, some “lines” have not yet a delivery date. with the second solution (add extra collumns), you can solve this with an IF formula.
Another issue I had once had is with imported data, you see a date, it is formated as a date but it is NOT a date. Work around: multiply with 1.Only then you could group.
Hi Bart,
Thanks for the suggestions! You are absolutely right and I forgot to mention that pivot table date grouping only works if all the cells in the date column contain a date value. Thanks again!
Very interesting article, nice to see the different methods outlined and analysed. I only found your site this week and it has already become one of my favourite resources, everything is explained so clear and concise.
You mentioned there is a quick way to produce the Date Table using power query. Would it be possible for a little more explanation on that or a link please? Thanks
Hi Terence,
Thanks for the great feedback! I’m happy you found me. 🙂
There is one link in the article to another blog on how to create a date table in Power Query. I don’t have any other articles at this time.
I do have an article on an overview of Power Query that should help get you started. And also an article on how to install Power Query.
I hope that helps. Thanks again!
In earlier versions of Excel you could choose between sharing the same pivot table cache or not. What I now do if I do not want to share the cache is using that ‘old’ command (add it to ribbon or Quick Access Toolbar).
From the list of All Commands, choose Pivot Tbale and Pivot Chart Wizard. When you make a pivot from a data source you allready use in the file, you’ll get an extra question about sharing the cache or not.
Sometimes I have more reasons then grouping issues to not share the cache!
Great tip Saskia! Thanks for sharing!
Hi, What to do if a date range is blank because it has to be blank. For example an ordered good has not yet been received to stock. I’m unable to use the group function and there are a few blank cells on my Goods Received Date.
Hi Ismail,
The pivot table date grouping feature will NOT work if there are any blanks in the source data. If you can’t replace the blanks with dates, then you will have to use the grouping in the source data method.
Hi Jon,
Very informative article. I’ve run into a roadblock about date formats. The dates column I’m working on is written in different formats. MM/DD/YYYY & DD/MM/YYYY. It’s a list of more than thousand data and I’mm unable to format all into one single format.
Hi Ismail,
You might want to try formatting the entire column to the General format to see if they are all numbers stored as dates. Here’s an article with more info on the date system in Excel. I hope that helps. Thanks!
I have created a pivot table with vba, now want add a slicer to filter by year, where as my data has dates. I know we can group manually but I don’t know how to do it with vba.
thanks
MS
Hi Malik,
To group fields in pivot tables we can use the Range.Group Method. You can also use the macro recorder to get the code. I hope that helps get you started.
Jon,
Thanks for the article. Helped me recently when I needed to turn off date grouping in a pivot table.
Regards,
Matthew
Thanks for your personal marvelous posting! I truly enjoyed reading it, you happen to
be a great author.I will ensure that I bookmark your blog and will often come
back later on. I want to encourage continue your great writing,
have a nice evening!
Hi John,
I’m trying to calculate the variance between Jan 17 & Jan 18, Feb 17 & Feb 18 etc in a pivot table. The source data has the dates all in one column (2017/01 etc) and the pivot table separates them into individual month columns. I can’t get it to create a calculated field though. Any tips at all?
Thanks John,
Paul
thanks for your perfect explanation sir
Hi there –
I’ve got a pivot table based on months and year. When I refresh the pivot, the data will probably change and those same months may disappear. However, it’s linked to another tab with all months and data underneath. How do I tell the this tab that month no longer exists in the pivot and update data accordingly? I can’t use a V-lookup … not sure what the right formula is?
Thank you
A very well structured and well written overview.
Thanks Tim Boulton
Can I just sort my data out by weekly.. I’ve been looking for days
[…] customize the groupings, especially for fiscal calendars.  I explain more in my articles on Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data.  I have another article on 3 Ways to Group Times in […]
Thank you so much! I was stuck with quarters and years and I had lost my months grouping and I didn’t know how to get it back. I had removed the field entirely from all my tables to try to get it to reset, but it wasn’t working. At first, this solution wasn’t working, because the grouping options were all gray when I had the field in the filter column. But, when I changed it to Rows, I was suddenly able to ungroup it! Now it works! Thanks for saving me a bunch of extra work trying to redo everything.
Great job!
You’re covering a lot on this topic, however you structured it so clear I was able to find what I was looking for quick and it’s awesome. So are you! Thank you!
my automatic grouping is turn on, but then also my dates are not getting group. Try tried manual by going to Analyze tab and select group selection, it’s not working all the way. Please help