Problem Grouping Pivot Table Items
If you try to group pivot table items in Excel, you might get an error message that says, "Cannot group that selection." For older versions of Excel, if you had a problem grouping pivot table items, it was usually caused by blank cells, or text in number/date fields. For Excel 2013 and later, there's another thing that can prevent you from grouping.
Problem Grouping Pivot Table
Here's a screen shot of the "Cannot group that selection." error message that appears. The message doesn't give you any clues as to why you can't group the items. It's up to you to do the detective work.
We'll look at the traditional reasons for this grouping problem in the next section. But first, here's the newer issue, that might affect you, if you're using Excel 2013 or later.
- When you create a pivot table, there's a check box to "Add this data to the Data Model".
- If you checked that box, you won't be able to group any items in the pivot table.
When the source data is added to the data model, you end up with an OLAP-based Power Pivot, instead of a traditional pivot table, and the grouping feature is not available.
Is It OLAP-Based?
A quick way to tell if your pivot table is OLAP-based is to check the Ribbon:
Select any cell in the pivot table
- On the Excel Ribbon, click the Analyze tab (under PivotTable Tools)
- In the Calculations section, find the OLAP Tools command.
- If it's dimmed out, your pivot table is the traditional type
- If the command is active, your pivot table is OLAP-based
And if you check the Fields, Items, & Sets drop down, some of the features will be dimmed out, for OLAP-based pivot tables. For example, you can't create a calculated field or calculated item.
Fix the Grouping Problem
I haven't found any way to change the pivot cache from an OLAP-based source (data model), to a data source that isn't in the data model. So, if you need grouping, create a new pivot table from the source data, and do NOT check the box to add the data to the Data Model.
NOTE: You can keep the OLAP-based pivot table too, and have two pivot tables based on the same data, using different pivot caches.
Blank Cells or Text
If your pivot table is the traditional type (not in the data model), grouping problems are usually caused by invalid data in the field that you're trying to group.
If you're trying to group dates or numbers, the grouping problem usually occurs when the field contains records with one of these items:
- a blank cell in a date/number field, or
- a text entry in a date/number field.
To fix the problem
- For blank cells, fill in the date/number (use a dummy date/number if necessary).
- If there is text in the date/number field, remove it.
- If numbers are being recognized as text, use one of the techniques to change text to real numbers.
Then refresh the pivot table, and try grouping the items again.
If you don't have blank cells or text in the date column, there may be a grouped field left over from a previous time that you grouped the data.
- Check the field list, to see if there's a second copy of the date field, e.g. Date2.
- If there is, add it to the row area, and ungroup it.
- Then, you should be able to group the date field again.
Pivot Table Grouping
Learn more about pivot table grouping, and get a workbook with sample file that you can use for testing. Go to the How to Group Pivot Table Data page on my Contextures website.