When you try to group dates in an Excel pivot table, or other pivot table items, you might get a pivot table error, “Cannot group that selection.” In Excel 2010, and earlier versions, that error was usually caused by blank cells in your source data, or text in the number or date columns. If you’re using Excel 2013 or later, there’s another reason that might prevent you from grouping pivot table items.
Cannot Group That Selection
Here’s a screen shot of the pivot table error, “Cannot group that selection.” that appears. As you can see, the error message doesn’t tell you WHY you can’t group the items. Excel leaves it up to you to find out what the problem is.
Data Model
You can run into pivot table grouping problems in any version of Excel. But if you’re using Excel 2013 or later, there’s a new reason for the error message, that might affect you. Here’s what can happen:
- 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.
OLAP-Based Pivot Table
If you added the source data to the data model, you created an OLAP-based Power Pivot, instead of a traditional (normal) pivot table. In OLAP-based pivot tables, the grouping feature is not available.
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
Limitations in OLAP-Based Pivot Tables
It’s not just grouping that is prevented In OLAP-based pivot tables. Other features are unavailable too.
For example, you can’t create a calculated field or calculated item.
No Fix for this Grouping Problem
Unfortunately, there’s no fix for grouping in an OLAP-based pivot table. If you want grouping, you’ll need a pivot table with its source data NOT added to the data model.
To get grouping:
- Create a second pivot table from the source data
- Do NOT check the box to add the data to the Data Model.
Keep the OLAP-based pivot table too, and you’ll have two pivot tables based on the same data, using different pivot caches.
NOTE: There’s no option or setting that lets you change the pivot table from an OLAP-based source (data model), to a data source that isn’t in the data model.
Grouping Error for Normal Pivot Tables
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.
For solutions to grouping error in Normal Pivot Tables, see the Pivot Table Grouping page on my Contextures website.
Videos: 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.
First, this video shows how to group Text items in a pivot table.
Next, this short video shows the basics of pivot table grouping
Related Articles
Pivot Table Grouping Affects Another Pivot Table
Stop Pivot Table Date Grouping
Grouping Pivot Table Dates by Fiscal Year
Grouping Pivot Table Dates by Months and Weeks
______________________
Excel Pivot Table Error Cannot Group That Selection
__________________
Thank you – you had the exact answer I needed. Much appreciated.
The check for data added to the data model was checked to allow count on distinct entries – I removed dups and did not check that box. Useful.
Hello Debra;
I’m trying to calculate how many products fall in different commissions. These commissions are calculated by dividing the commission paid by the price of the product (most of the resulting values have 8 decimals).
Pivot Tables won’t summarize the percentages.
I tried to group the percentages but gives me the Error Cannot Group That Selection.
Is there a soution?
Thanks,
Miguel