Cannot Group That Selection Error in Pivot Tables

Grouping data such as dates, numbers, or text in a Pivot Table can help you to summarize and analyze data more effectively.

Additionally, it can make it easier to identify significant trends and patterns in your data. 

However, sometimes when you attempt to group particular data in a Pivot Table, Excel does not display the Grouping dialog box to allow you to group the data but instead shows a warning message indicating that it cannot group that selection.

cannot group that selection error in Excel Pivot table

The “Cannot group that selection” error in Pivot Tables generally indicates a problem with the underlying source data.

This tutorial describes four causes of the ‘Cannot group that selection’ error in Pivot Tables in Excel and offers possible solutions.

Cause #1: Unsupported Date Format 

If you have a column of dates, but all or some of the values are in a format not supported by Excel (or are in a text format), you won’t be able to group them by months, quarters, or years.

Also, note that only leap years have a 29th February date; therefore, a 29th February date in a typical year will trigger the “Cannot group that selection” error.

Let’s consider the following Pivot Table with a date in an invalid format in cell A3. 

pivot table with an unsupported date format

We want to group the dates in column A by months using the steps below:

  1. Select any date in column A, right-click the selection, and choose Group on the shortcut menu.
right click on the pivot table column and then click on the group option

Excel does not display the Grouping dialog box to allow you to group the dates but instead shows a warning message indicating it cannot group that selection (as shown below).

it shows the cannot group that selection error in pivot table

How to Fix this ‘Cannot Group that selection‘ error

The only way to fix this error is to reenter the invalid date in the source dataset in a format supported by Excel. You can then refresh the Pivot Table and then group the data again.

However, it can be challenging in a large dataset to spot the cell or cells with invalid dates easily. You can use the below steps to make it easy to identify the cells with dates in a format unsupported by Excel:

  1. Insert a helper column in the source dataset as shown in the example dataset below:
add a helper column in the data set that was used to create the pivot table
  1. Select cell B2 and enter the formula =A2+1. This formula adds the value 1 to the data in cell A2.
  2. Double-click or drag the fill handle feature to copy the formula down the column.
enter the simple edition formula in the helper column to identify dates that are in the unsupported date format

The formula returns the #VALUE! Error if the referenced cell contains a date in a format unsupported by Excel.

In this case, the date in cell A7 has an invalid format, and we need to fix it. Therefore, we reenter the date in the format “yyyy-mm-dd,” as shown below:

Enter the date in the right format

Notice that cell B7 no longer has the #VALUE! error. We can now delete the helper column.

  1. Right-click any cell on the Pivot Table, and click Refresh on the shortcut menu. This will refresh the Pivot Table and show us the changes that we made in the source data
refresh the pivot table to update it using the source data
  1. Right-click any cell with a date on column A and choose Group on the shortcut menu. 
right click on any date in the column and then click on the group option

Because all the dates are in a format supported by Excel, Excel now returns the Grouping dialog box.

  1. Select Months on the Grouping dialog box and click OK.
Select Month in the Group dialog box

Note: You can change the starting and ending dates on the Grouping dialog box if the default values are unsuitable for your work.

The dates on column A of the Pivot Table are now grouped by months:

the date columning to the table has now been grouped based on the months

How to Filter the Cells With #VALUE! Error

We have only one #VALUE! error in the helper column. However, sometimes you can have many cells with the value error.

pivot table source data set with the helper column where we have the value error

If there are many cells with the value error, you can filter the cells to quickly review and fix the errors without unnecessarily scrolling the worksheet. 

You can use the steps below to filter the cells with the value error:

  1. Select the cell range B1:B15 in the helper column.
  2. On the Data tab, click the Filter button on the Sort & Filter group.
click on the filter icon in the ribbon
  1. Open the filter drop-down in cell B1, and deselect all the options in the bottom area except the #VALUE! option and click OK.
open the filter drop-down for the helper column and only select the value error option and uncheck everything else

The cells with the value error are selected, and you can now quickly review the errors and fix them accordingly. 

All value error records are filtered
Also read: How to Fix Pivot Table Field Name is Not Valid Issue

Cause #2: Text Strings in a Numeric Column

If you have a column that is supposed to contain only numeric values, but some are text strings, you won’t be able to group the values in a Pivot Table. 

Suppose we have the following Pivot Table with a text string that looks like a number in cell A15. 

Dataset where there is text string that looks like number

We want to group the values in column A in groups of 10,000s. We use the steps below:

  1. Right-click any number in column A of the Pivot Table and choose Group on the shortcut menu. 
Right-click and then click on Group

Excel does not group the data and displays the warning message that it cannot group that selection.

Can not group that selection prompt shows up

How to Fix this ‘Cannot Group that selection‘ error

To fix the error, ensure that all the entries in the underlying column are numbers. Next, refresh the Pivot Table and then group the data.

However, spotting the cell or cells with text strings can be challenging in a large dataset. You can use the below steps to make it easy to identify the cells with text strings, especially those that look like numbers:

  1. Insert a helper column in the source dataset.
Insert a helper column
  1. Select cell D2 and enter the following formula:
=ISNUMBER(C2)

Note: The ISNUMBER function checks whether a given value is a number. The function returns TRUE if the value is a number and FALSE if not. 

Enter the ISNUMBER formula
  1. Drag or double-click the fill handle feature to copy the formula down the column.
Apply the formula for the entire helper column

The formula returns FALSE in cell D3 because the value in cell C3 is not a number but a text string. 

Notice that the value in cell C3 has a space between the digit 1 and the zeroes. So it looks like the number 1000, but it is a test string. 

Remove the space between the digit 1 and the zeroes, and the value becomes a number.

Correct the entry that was leading to text string

We can now turn to the Pivot Table and do the following steps:

  1. Right-click any cell on the Pivot Table and click Refresh on the shortcut menu.
Refresh the Pivot Table
  1. Right-click any cell with a number in column A of the Pivot Table and choose Group on the shortcut menu.
Right-click and then click on Group
  1. Click OK on the Grouping dialog box to accept the default settings. 
Group the data in the grouping dialog box

Note: You can change the default settings if they are unsuitable for your work.

The numbers in column A of the Pivot Table are now grouped.

Pivot Table has been grouped

Note: In case you have blank cells in the columns, it would also be treated as text and you would see the error prompt

Also read: Delete a Pivot Table in Excel

Cause #3: The Column You Want to Group is a Text Column

While you can group text items in a column in Pivot Table, you need to select the items you want to group and then click on the group option.

Unlike the grouping of a column with numbers, if you just right-click on one cell and then use the group option, it will show you the prompt that says that it can not group these items (which is because it does not know what items to group together).

Suppose we have the following Pivot Table with a text column A.

Pivot Table with a text column A

We want to group the first three items in column A into “New Branches” and the last two into “Old Branches.”

If I right-click on any single cell and then click on the Group option (as shown below), it will show the error prompt:

Cannot group that selection prompt

How to Fix this ‘Cannot Group that selection‘ error

You need to manually select the text items in column A of the Pivot Table that you want to group and then group them.

To do that, use the steps below:

  1. Select the first three branches in cells A5, A6, and A7. Right-click the selection and choose Group on the shortcut menu.
Click on the group option

A default Group1 is automatically created:

A default Group1 is created

Not that any cell that is not in the selected group would automatically be assigned to a default group of its own. For example, South is automatically assigned to a group called South.

  1. Select cell A10, press and hold down the Ctrl key and select cell A12. Right-click the selection and choose Group on the shortcut menu.
Select the non adjacent cells and then click on the group option

A default Group2 is automatically created:

A default Group2 is automatically created
  1. Rename the groups by selecting cells A5 and A9 and typing “New Branches” and “Old Branches,” respectively.
Rename the groups in Pivot Table

Cause #4: Pivot Table Was Added to a Data Model

If you select the Add this data to the Data Model check box when creating a Pivot Table, 

Checking the Add this data to data model

…you won’t be able to group items on the Pivot Table:

Can not group that selection prompt

How to Fix this ‘Cannot Group that selection‘ error

Create another Pivot Table based on the same source data but do not select the Add this data to the Data Model check box. 

This tutorial explained four causes of the “Cannot group that selection” error in Pivot Tables and offered possible solutions. We hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment