Home » Our Blog » Excel – Group dates by month in a Pivot Table

Excel – Group dates by month in a Pivot Table

Sharyn Baines

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 2016, 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 pivot table dates

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

4. The Grouping dialog box will be displayed.

pivot table group months as dates

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 2013 and January 2014 and 2015 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.

pivot table show months as dates

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.

Sharyn is an expert trainer.  She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand.

She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

If you enjoyed this you may also enjoy...

Excel Tables

Managing, analysing and referencing lists of data becomes easier when you know how to use Excel Tables. There are so many advantages to using Tables which I will cover within this post. Once you see how helpful they are I’m pretty sure you will be using Tables a lot. When you insert a Table over a list

​Read More

Excel – Using VLOOKUP to compare two lists

If you want to save yourself a huge amount of time when reconciling data, learn how to use VLOOKUP to compare two lists or columns of data. Years ago the Training and Development manager of a large organisation invited me to spend some time in their Accounts department in order to observe how the Accounts team

​Read More