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:
- Create the Pivot Table, placing the date field into the column or row area.
- 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).
3. From the Analyze tab (Options tab 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 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.
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.
- Press ALT + F11. This will open Visual Basic
- 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.
If you enjoyed this you may also enjoy...
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
Learn how to change a Pivot Table back to the classic display of version 2003 and earlier. If you were familiar with Pivot tables in Excel versions prior to Excel 2007 you will be very used to the classic display of working with the blue lines of the Pivot Table and the ability to drag and
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
There are times when you want to return a match for a VLOOKUP based on more than one value. For example, we may have customers who have multiple branches. Each branch is recorded under the same customer number. So how do we look up info for a specific branch if all of the branches are
Finding and removing duplicate data in Excel can be a quick process. However, for many people who haven’t learnt how to do this the quick way in Excel, it can be an absolute chore. One of the many things I love about my job is that I get to work with so many different people from
PROBLEM: having to retype text when your data is in UPPERCASE, lowercase or a Mix oF bOTH. SOLUTION: quickly change the text back to the proper case without having to retype it.