Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data - Excel Campus
16

Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data

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

Pivot Table Date Groups vs Source Data Date Groups in Excel

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:

  1. We can use the Group Field feature of the pivot table.
  2. We can create additional columns in the source data with text or numeric values using formulas.
  3. 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.

Group Field Pivot Table Feature Does Not Add Group Fields to the Source Data

After we create the groups on the Grouping menu, new fields are added to the pivot table field list.  In Excel 2016 this grouping happens automatically when you add a date field to one of the areas in the pivot table.

Even though we see new 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.

The Pivot Table Group Field feature adds the fields to the pivot table field list, but NOT the source data.

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.

PivotPal Pivot List Feature Shows List of Pivot Tables and Cache Index Number

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.

Drill Up or Down on Grouped Dates in Pivot Chart

#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.

Add Columns to the Source Data for the Date Groups

This means that the grouped fields will actually be columns in the source data, and just stored in the pivot cache.

Pivot Table Groups Based on Columns of Numbers or Text in Source Data

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.

Use Calculated Columns for Date Groupings in Pivot Tables for Fiscal Calendars

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.

The Date Grouping Formulas Require 2 digit number in front of month name for fiscal calendar sorting

#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.

Calendar Table or Date Table Used to Create Date Groupings in the Data Set

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.

Power Pivot Creates Relationships between Tables to create Date Groups

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.

Power Pivot Pivot Table use fields from different tables in the same pivot table

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.  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!

Click here to leave a comment on the post

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 16 comments
undercabinetradio.us - August 28, 2017

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!

Reply
MatthewS - August 5, 2017

Jon,

Thanks for the article. Helped me recently when I needed to turn off date grouping in a pivot table.

Regards,
Matthew

Reply
Malik - February 24, 2017

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

Reply
Ismail Faisal - July 23, 2016

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.

Reply
    Jon Acampora - July 31, 2016

    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!

    Reply
Ismail Faisal - July 22, 2016

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.

Reply
    Jon Acampora - July 31, 2016

    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.

    Reply
Saskia - July 6, 2016

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!

Reply
Terence - June 3, 2016

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

Reply
    Jon Acampora - June 8, 2016

    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!

    Reply
Bart - May 16, 2016

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.

Reply
    Jon Acampora - May 17, 2016

    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!

    Reply
John - May 11, 2016

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

Reply
    Jon Acampora - May 12, 2016

    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.

    Reply

Leave a Reply: