Grouping is a powerful feature in PivotTable reports but sometimes Excel won’t let you apply grouping. There are a few reasons for this.

Grouping allows you to create hierarchies in your report that may not exist in your data. The most common type of grouping is date grouping. A Date field (column) can be grouped by Days, Weeks, Months or Years. The data doesn’t need to have a field (column) for those categories.

Below is a before and after example of the date grouping structure.

To apply date grouping right click the Date field heading on the PivotTable and choose Group

Select the type of grouping and click OK.

Note you can use the Ctrl key to select two or more items.

If your table has multi-year data you will need to use Month and Year to group – see image below which used the Ctrl key to select both options.

Once you apply grouping then it will be available in future pivot tables you create using the same data.

If you try to apply grouping to a Date column you may get the following error message.

This means that there are non-date entries in the Date column. You will need to examine the records to find out what is causing the issue.

Unfortunately it only takes one invalid entry in the field (column) to stop the whole grouping process. The types of entries that can cause issues

• Blanks – a cell with nothing in it will stop Date grouping
• Text – it may look like a date, but it may be a text entry in the Date column that stops grouping
• Errors – any Excel errors eg #N/A or DIV/0, will also stop any type of grouping, including Date grouping

Fixing the issue

Sometimes even after you have fixed the issue eg entering a date in a blank cell, Excel still won’t let you Group, even after you refresh the data. Excel remembers there was a problem with the column and stops the grouping.

Unfortunately you will need to start the PivotTable from scratch to have the change recognised.