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!

A video at the end of this tutorial covers the grouping of date, numeric and text fields – a great overview of this topic. We recommend in house Excel Training with Blue Pecan Computer Training.

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.

25-07-2016 14-51-54

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.

25-07-2016 14-56-03

In the same way you can expand a quarter to display months

25-07-2016 14-57-43

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).

25-07-2016 15-01-42

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.

25-07-2016 15-07-07

Collapsing Rows

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).

Ungroup Dates

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.

25-07-2016 15-28-01

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.

25-07-2016 15-18-57

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.

25-07-2016 16-04-59

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.

25-07-2016 15-28-01

In the Grouping dialog select the fields you want to group by and then click OK to confirm.

25-07-2016 15-28-52

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.

25-07-2016 15-32-44

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.

25-07-2016 15-47-26

It also appears as Date in the Rows area.

25-07-2016 15-50-41

To change the field’s name, right-click on the Date field in the Rows area and select Field Settings from the menu.

25-07-2016 15-53-05

Type your new field heading in the Custom Name box. Click OK to confirm.

25-07-2016 15-54-03

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.

25-07-2016 16-10-20

Data pivoted on a date in rows and columns.

25-07-2016 16-14-56

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.

25-07-2016 16-29-13

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.

Please watch our Mousebytes video on grouping data in PivotTables. As well as showing you how to group date fields, it also demonstrates how to group text and numeric fields. Happy viewing!

Leave a Reply

Your email address will not be published. Required fields are marked *

2 × 5 =