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.
Was this blog helpful? Let us know in the comments below.
If you enjoyed this you may also enjoy...
[Watch on YouTube] / [Subscribe to our YouTube Channel]Need to learn how to quickly remove extra spaces in an Excel cell? Extra spaces can appear in your worksheet for lots of different reasons such as someone accidentally typing them in or extra characters traveling with data exported out of another software product. In this Blog we will
[Watch on YouTube] / [Subscribe to our YouTube Channel]It can be frustrating when you’re trying to type a zero in front of a value or a number in Excel and it just disappears. In this blog we will cover easy ways to take control of your zeros.How to add 0 in front of a number while
[Watch on YouTube] / [Subscribe to our YouTube Channel]In this blog we will cover how to change the case of your text in Excel with or without a formula. If you’re a keen user of the Change Case button in Word, Outlook or PowerPoint you may be wondering where it is in Excel.The Change Case button
[Watch on YouTube] / [Subscribe to our YouTube Channel]Want to learn how to remove those pesky stubborn spaces that even the TRIM function can’t get rid of? Here is an easy fix that will have you up and running and getting rid of your spaces before you know it.When TRIM function doesn’t work on numbersThere’s no doubt
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