[Fixed] Excel Pivot Table: Cannot Group That Selection

In Excel Pivot Table, you can group numbers or dates. Grouping number or dates help us analyze data more effectively. Excel has a command that can group numbers and dates automatically. But if the cell format changes or cells contain any invalid data, then Excel cannot group those numbers and dates. In this article, I will discuss the reasons for which the Excel Pivot Table shows the ‘Cannot group that selection’ error. I will also try to come up with all the possible solutions. So let’s get started.

Reasons for Occurring ‘Cannot Group That Selection’ Error in Excel

The main reasons for which Excel shows the ‘Cannot group that selection’ error are,

• Blank cells in the midst of numbers or dates
• Texts with numbers or dates
• Invalid format of numbers or dates

How to Use Excel VBA to Detect Causes of ‘Cannot Group That Selection’ Error

Here, I’m providing you can a VBA code. You can use this code to check a cell format simply by referring to a cell. When you get a cell format other than numbers or dates, convert them all into numbers or dates. Now Excel will group similar numbers and dates without popping any error.

I will use the following Product Order Record as a dataset to demonstrate all the solutions. In the dataset, I’ve different product names with their amount and country. I also have all the products’ corresponding Order Date and Ship Date.

I will try to group all the Ship Dates. But look, the Ship Date column has a mix of various date formats as well as invalid dates.

Now will create a user-defined function to detect all the cell types in the Ship Date column.

For that,

• Press ALT + F11 to open the VBA Editor.
• Now go to Insert ➤ Module to create a new module.

• Now insert the following VBA code in the VBA Editor.

Public Function ValueType(c)

    Application.Volatile
    Select Case True
        Case IsEmpty(c): ValueType = "Blank"
        Case Application.IsText(c): ValueType = "Text"
        Case Application.IsLogical(c): ValueType = "Logical"
        Case Application.IsErr(c): ValueType = "Error"
        Case IsDate(c): ValueType = "Date"
        Case InStr(1, c.Text, ":") <> 0: ValueType = "Time"
        Case InStr(1, c.Text, "%") <> 0: ValueType = "Percentage"
        Case IsNumeric(c): ValueType = "Value"
    End Select

End Function

The above code creates a user-defined function called ValueType. If you insert any cell address inside the function, it can detect the cell type of the cell address.

• Now create an additional column called Data Type.
• Insert the following formula in cell G5.

• Then press ENTER.

This formula will return the cell type of cell the F5.

Now drag the Fill Handle to copy down the formula to the end of the Data Type column.

Now you will get the cell types of all cells of the column called Ship Date.

As you can see, there are 3 types of cell formats in the Ship Date column.

They are

• Date
• Blank
• Text

Convert all the Blank as well as Text cell formats to Date. Now Excel won’t show the error ‘Cannot group that selection’.

1. Invalid Data Format Results in ‘Cannot Group That Selection’ Error in Excel Pivot Table

Look at the following Ship Date column. All the red marked dates are invalid date format.

• Now I select a date.
• Right-click on it.
• Then choose the Group option.

Excel shows a ‘Cannot group that selection’ error.

The main reason behind this error is the invalid date formats.

Now I’ve corrected all the invalid date formats. I maintained the format Month-Day-Year.

After that, I again tried to group the dates. This time no error occurred.

The Grouping dialog box has appeared.

I selected Months and click OK.

So all the dates have been grouped by Months. This time Excel didn’t show any error.

Read More: How to Group Rows in Excel Pivot Table

2. Blank Cells in the Dataset Causes ‘Cannot Group That Selection’ Error in Excel Pivot Table

Blank cells in the midst of dates can also lead to the ‘Cannot group that selection’ error.

In the Ship Date column, you can see some blank cells.

• Now I right-click on a date in the Pivot Table.
• Then I clicked on the Group command from the context menu.

Excel again showed a ‘Cannot group that selection’ error.

To solve this problem, I filled up all the blank cells with relevant dates.

After that, I again tried to group the dates. This time no error occurred.

The Grouping dialog box has appeared.

I selected Months and click OK.

So all the dates have been grouped by Months. This time Excel didn’t show any error.

Read more: Pivot Table Custom Grouping

Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.

Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

Conclusion

To sum up, we have discussed 2 quick and effective solutions to fix ‘Cannot group that selection’ in Excel Pivot Table. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.

Related Articles

• How to Group Columns in Excel Pivot Table
• How to Group Numbers in Excel Pivot Table
• How to Use Excel Pivot Table to Group by Different Intervals
• How to Rename a Default Group Name in Pivot Table
• How to Make Group by Same Interval in Excel Pivot Table

<< Go Back to Group Pivot Table | Pivot Table in Excel | Learn Excel