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!