This Mousebytes explains how to group dates in a PivotTable. When you have a date field in a PivotTable it is often useful to group dates by week, month, quarter or year. If you have Excel 2016 or later then you are in luck as dates are automatically grouped for you. However, in earlier versions of Excel it is not too difficult to apply grouping. The video covers the grouping of date, numeric and text fields – a great overview of this topic. We hope you find this Mousebyte tasty and wholesome!
This tutorial assumes you have created your PivotTable and have added your date field to the rows or columns area.
Grouping Dates in Excel 2016 and Later
When you add a date field to the rows or columns area of a PivotTable in Excel 2016 or later, dates are automatically grouped by Years, Quarters and Months. If this doesn’t happen automatically then there will be something not quite right with your data – this will be covered later.
The PivotTable below shows data grouped by year.
Expand a Single Row
To the left of each year heading is an expand button. Click on an expand button to display quarters for a particular year.
In the same way you can expand a quarter to display months
Alternatively, you can expand a row by right-clicking on the year value and selecting Expand/Collapse in the menu. In the sub-menu select what to expand to – quarters or months (months will actually be labelled with your date field heading – we will look at how to fix this later).
Expand All Rows
To expand all rows (or years), right-click into any year row and select Expand/Collapse in the menu. In the sub-menu select either Expand to Quarters or Expand to Month (again Month may be displayed with your date field heading). Then right-click again, select Expand/Collapse and then Expand Entire Field.
Collapsing rows works in the same way as expanding. Obviously whereas your expand button appears as a + (plus) your collapse button appears as a – (minus).
If you don’t want grouping to be applied to your date field, simply right-click into any row or column heading containing a date value and select Ungroup from the menu.
Customise the Grouping of Dates
You can easily change the default grouping applied to date fields. Do this by right-clicking on any row or column heading containing a grouped date value and select Group from the menu.
In the Grouping dialog, click on a grouping By option to select or unselect it. So if I no longer want to group by quarters, I would click on Quarters to deselect it. If I wanted to group by days I would click on Days as it is not selected. Click on OK to confirm.
Alternatively, you can use the PivotTable Field list to customise the way your dates are grouped. All you need to do is tick or untick any of the date fields that are shown in the list. However, if the grouping option that you want to apply is not in the field list, you will need to use the method described above.
Grouping Dates in Excel 2007, 2010 and 2013
Dates are not automatically grouped in PivotTables created in versions of Excel prior to Excel 2016. To group dates in these versions, simply right-click on a row or column heading containing a date value and select Group from the menu.
In the Grouping dialog select the fields you want to group by and then click OK to confirm.
Grouping by Weeks
One notable omission from the group by list in the Grouping dialog, is weeks. To group by weeks you need to select Days in the By list and then set the Number of days setting to 7. Set your Starting at: date as the first day of the week you want to start grouping in. Click OK to confirm.
Changing the Name Given to Grouped Fields
One thing you will notice when you group fields is that the smallest unit of grouping is never named properly. For example, if I grouped by years and months, the month field wouldn’t be named months as it would retain the original date field’s name. In the Field List example below, years and quarters fields have been named correctly but the month field is named Date.
It also appears as Date in the Rows area.
To change the field’s name, right-click on the Date field in the Rows area and select Field Settings from the menu.
Type your new field heading in the Custom Name box. Click OK to confirm.
Put Date Groupings in Different Pivot Table Areas
When you group dates you effectively create separate fields for each level of grouping. This gives you lots of flexibility in terms of how you Pivot values by date. For example, if I have grouped by year and month I don’t need to keep both fields in the same area as I can drag, say Month, to the Columns area whilst keeping Years in the Rows area.
Data pivoted on a date in rows and columns.
Dates Fields that Won’t Group
If you try to group by dates and this dialog appears saying “Cannot group that selection”, check the date column in your data source. Look for any values that are not actually dates.
Dates are usually right-aligned in a cell, so this can help you spot a rogue value which will be left-aligned. This is because dates are essentially numbers which are also right-aligned. Unfortunately, if you enter a date in the wrong format for your region or enter a date that doesn’t exist such as 31 November, the date will be stored as a text value.
Another way of identifying text values is to sort a date column in ascending order – oldest to newest. Any text values will end up at the bottom of the list.
Once you have fixed any problems, refresh your PivotTable and you should be able to group by date.