Fix Pivot Table Problem – Cannot Group That Selection

In an Excel pivot table, if you try to group a date or number field, you might see an error message. The message box shows a yellow warning label, and the text says, “Cannot group that selection”.

Error Message Troubleshooting

There are 2 common causes for this pivot table grouping error message:

Data source has some blank cells
Pivot table was added to Data Model

The video below shows examples for these two problems, and there are written steps below the video.

Video: Cannot Group that Selection

In this short video, I show two examples for the “Cannot group that selection” error message. There are details below the video.

Video Timeline

0:00 Error – Cannot Group That Selection
0:12 Problem Grouping Dates
0:34 Fix Dates Problem
1:19 Test the Pivot Grouping
1:35 Problem Grouping Text
1:55 Pivot Table Setting
2:23 Avoid Pivot Grouping Problem

1) Blank Cells or Text

If you have a normal pivot table (not in the data model), the grouping problem usually happens if the source data has records with

a blank cell in a date/number field, or
a text value in a date/number field.

To fix the problem with blanks or text:

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.
Tip: For help with changing the text to real numbers, go to the Fix Excel Numbers page on my Contextures site.

2) Pivot Table Added to Data Model

When you created the pivot table, did you check the box to “Add this data to the Data Model”?

If so, you won’t be able to group any items in the pivot table. Instead of a normal pivot table, it is an OLAP-based Power Pivot, and the grouping feature is not available.

Here are a couple of workarounds to fix this problem:

If you need to group the data, create another pivot table, based on the same data, but do NOT check the Add this data to the Data Model” option
Add a new column in the source data, and enter grouping categories in that column. Then, refresh the pivot table, and add the new field to the pivot table layout.

Get the Sample File

To get the Excel sample file that I used in the video, and more information, go to the Pivot Table Grouping page on my Contextures site.

______________