Excel – Group dates by month in a Pivot Table

Posted April 21, 2015

To group dates by month in a Pivot Table is easy when you know how. Unfortunately, displaying date values in a Pivot Table can produce unexpected results.

For example, in the Pivot Table below I have added a Date field to the Column area of my Pivot Table.  Every date in the underlying data table is different, therefore resulting in a column for every day and a very large Pivot Table. Fortunately the Group command allows me to group the date fields into months.

Group dates as months in a Pivot Table

Note: In Excel dates are automatically grouped. You can use the Group Field option to edit how the dates are grouped should you need to.

To do this:

  1. Create the Pivot Table, placing the date field into the column or row area.
  2. This next step is important.  Select a cell on the Pivot Table that holds the date value, as shown below in cell B4, (if you miss this step the Group Field button will be inactive).

Group dates in Pivot Table 1

3. From the Analyze tab (Options tab in Excel 2010) in the Group group click Group Field.

4. The Grouping dialog box will be displayed.

Group dates in Pivot Table 2

5. From the By list select how you want the data grouped.

Note: you can select more than one option from the ‘By’ list. Therefore if you would like to group a list of dates by Month and Year click both options from the list. Be aware that if you select ‘Month’ all entries for a month will be grouped, i.e. all entries for January 2020 and January 2021 and 2022 will be grouped together.  Therefore if your data includes dates from several years be sure to select Month and Year.When grouping by ‘Days’ the Number of days option will become active.  Select the number of days you wish to group by, e.g. setting the option to 7 will group the items by week.

6. Click OK.  The data will be grouped accordingly.

Group dates in Pivot Table 3

Note: dates are automatically grouped in Excel 2016. To change how the dates are grouped follow steps 3 to 6 above.

 

Why do the date fields on all Pivot Tables in the workbook group as well?

When you create a Pivot Table report Excel automatically stores a copy of the source data in a part of your computer’s memory called the data cache.  Pivot Tables within the same workbook that are based on the same cell range or data connection share the same data cache.

There are times when sharing the same data cache can cause problems. One of those times is when within the same file you are grouping a date field into Months on one Pivot Table and then wanting no date grouping on another Pivot Table.

Creating a separate Pivot Table cache

Thankfully you can create a separate data cache for your reports.  However be aware that this may impact on the size and performance of your file.

If a cell range or data connection for two or more Pivot Table reports is different, the data cache cannot be shared between those reports.  To stop sharing a data cache in a Pivot Table report you can temporarily redefine the data range to force Excel to stop sharing the data cache.  For example, reset $A$3:$W$7555 to $A$3:$W$7556. This will create a 2nd cache.  Then reset the data range back to the original settings e.g. $A$3:$W$7555.

How many cache do I have?

You can check how many data cache there are in a file.

  1. Press ALT + F11.  This will open Visual Basic
  2. Press CTRL + G. In the Immediate window type ?ActiveWorkbook.PivotCaches.Count and then press ENTER.  The number of cache will be displayed.

We cover this Pivot Table feature in our very popular Excel Data Cleaning and Reporting course. If you are keen to become confident in creating Pivot Tables this course is a must.

Was this blog helpful? Let us know in the Comments below.

Group dates by month in a Pivot Table Excel

Excel – Group dates by month in a Pivot Table

To group dates by month in a Pivot Table is easy when you know how. Follow this step-by-step guide to learn how to group dates by months in a Pivot Table.

Instructions

Group dates as months in a Pivot Table

In Excel, dates are automatically grouped. You can use the Group Field option to edit how the dates are grouped should you need to. To do this:

  1. Create the Pivot Table, placing the date field into the column or row area.
  2. Select a cell on the Pivot Table that holds the date value (if you miss this step the Group Field button will be inactive).
  3. From the Analyze tab (Options tab in Excel 2010) in the Group group click Group Field.
  4. The Grouping dialog box will be displayed.
  5. From the By list select how you want the dates grouped.
  6. Click OK. The data will be grouped accordingly.

Notes

Group dates as months in a Pivot Table

Note: you can select more than one option from the ‘By’ list. Therefore if you would like to group a list of dates by Month and Year click both options from the list. Be aware that if you select ‘Month’ all entries for a month will be grouped, i.e. all entries for January 2020 and January 2021 and 2022 will be grouped together. Therefore if your data includes dates from several years be sure to select Month and Year. When grouping by ‘Days’ the Number of days option will become active. Select the number of days you wish to group by, e.g. setting the option to 7 will group the items by week.

Why do the date fields on all Pivot Tables in the workbook group as well?

When you create a Pivot Table report Excel automatically stores a copy of the source data in a part of your computer’s memory called the Pivot Table cache. Pivot Tables within the same workbook that are based on the same cell range or data connection share the same data cache. Thankfully you can create a separate data cache for your reports. However be aware that this may impact on the size and performance of your file.

Did you try this?

If you found this blog helpful please leave a comment below.

Elevate your Excel game and become a pro with our exclusive Insider Group

Be the first to know about new tutorials, videos, and tips for Microsoft 365 products. Join us now and claim your exclusive bonus, your list of Essential Excel Skills to become proficient in Excel!

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Skip to Instructions
>