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. Unfortunately 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.
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).
- From the Analyze tab (Options tab Excel 2010) in the Group group click Group Field.
- The Grouping dialog box will be displayed.
- From the By list select how you want the data grouped.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.
- 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.