[Fixed] Cannot Group that Selection Error in Excel Pivot Table

Pivot Tables in Excel are mostly used for grouping data, whether consolidating dates into months or sales figures into revenue ranges. However, attempting to group data sometimes results in the “Cannot group that selection” error. This error rarely indicates a mismatch between the data type in the source range and the type of grouping you are trying to apply.

In this article, we will explore the three most common causes of the “Cannot group that selection” error and provide step-by-step fixes for each scenario.

When you try to group a Date field by months, years, or quarters, Excel expects the entire column to contain valid, uniform date values. If even a single cell contains text, an incorrect separator, or a mixed format, the entire column fails to group, and the error occurs.

Let’s use a sample dataset with some dates incorrectly formatted (e.g., 12-01-20##, 03-002-2024, 18/02/24).

First, let’s create the Pivot Table from this faulty data.

➤ Select the data.
➤ Go to Insert and click PivotTable.

➤ In the dialog box, choose New Worksheet and click OK.

After setting up the Pivot Table with the Date field in the Rows area.

➤ Right-click on any date cell in the Pivot Table.
➤ Select Group.

The “Cannot group that selection” error message appears.

To resolve this, you must fix the dates in the source data and refresh the Pivot Table.

➤ Go back to your source sheet.
➤ Manually correct the incorrectly formatted dates.

For example, change 12-01-20## to 12-01-2024, 03-002-2024 to 03-02-2024, and 18/02/24 to 18-02-2024.

Now, update the Pivot Table with the clean data.

➤ Go back to the Pivot Table sheet.
➤ Click anywhere in the Pivot Table.
➤ Go to the PivotTable Analyze tab.
➤ In the Data group, click the Refresh dropdown.
➤ Select Refresh.

After refreshing, try grouping again.

➤ Right-click on a date in the Pivot Table.
➤ Select Group.

The Grouping dialog box opens successfully.

➤ Select Days and Months and click OK.

The Pivot Table now correctly displays the dates grouped by Month and Day, confirming the fix.

The same logic applies to numeric fields. If you attempt to group a column of sales figures (e.g., grouping by ranges of 100 or 1000) but that column contains non-numeric text (like spaces, accidental letters, or symbols that Excel doesn’t recognize), the grouping will fail.

Here, the Total Sales column is meant to be numeric, but some values are entered incorrectly (e.g., 8 00 instead of 800, or a cell with non-standard text/formatting).

Following the previous method, let’s create a Pivot Table from this data.
➤ Select the Data and click Insert > PivotTable.
➤ Choose New Worksheet and hit OK.
The Pivot Table will be created.
➤ Drag the Total Sales and Product in the Rows area.

➤ Right-click on a sales value in the Pivot Table.
➤ Select Group.

The “Cannot group that selection” error appears.

➤ Go back to your source data.
➤ Correct the problematic entries in the Total Sales column, ensuring they are only numbers.

➤ Refresh the Pivot Table by clicking PivotTable Analyze > Data > Refresh > Refresh.

The Pivot Table now reflects the clean data.

➤ Right-click any sales figure.
➤ Select Group.

➤ The Grouping dialog box opens.

You can now successfully group the sales figures, for example, by a range of 100.

The Pivot Table successfully displays the sales grouped, removing the “Cannot group that selection” error.

If you create your Pivot Table while checking the “Add this data to the Data Model” option, certain default functionalities change. Crucially, Pivot Tables based on the Data Model do not allow automatic grouping of text/product fields.

To check this error.

➤ Select any cell from the source table and click Insert > PivotTable > From Table/Range.

➤ Checkmark the “Add this data to the Data Model” option and click OK.

After that, the Pivot Table will be created. If we try to group text (like Product names) in a Data Model-based Pivot Table, the error will occur.

➤ Select some product names and click Group from the context menu.

The “Cannot group that selection” error appears.

The simplest fix is to recreate the Pivot Table without enabling the Data Model.

➤ Select your original data.
➤ Go to Insert and click PivotTable > From Table/Range.

➤ In the dialog box, ensure the “Add this data to the Data Model” checkbox is unchecked.
➤ Click OK.

After setting up the new Pivot Table (without the Data Model).

➤ Select some product names and right-click to open the context menu.
➤ Select Group.

Here, the items are instantly grouped under Group1, which can be renamed. Finally, we have grouped data from the Pivot Table successfully.

Frequently Asked Questions

How does enabling the Data Model affect grouping?

If you load your Pivot Table using the Data Model, Excel disables manual grouping. To fix this, create the Pivot Table without adding it to the Data Model.

Can I still analyze my data without grouping if this error persists?

Yes, you can use filters, slicers, or calculated fields as alternatives, but fixing the data formatting is the recommended solution.

Why can’t I group when using data from Power Query or external sources?

When the data is loaded into the Data Model, Excel disables manual grouping. You need to build the Pivot Table without adding it to the Data Model.

Concluding Words

Above, we have explored and solved three major issues about the “Cannot group that selection” error in Excel Pivot Table. This error indicates that you need to examine your data types or Pivot Table settings. By standardizing your date and numeric fields, ensuring you don’t use the Data Model when simple text grouping is required, you can quickly fix the error. If you have any questions, please don’t hesitate to let us know in the comments section below.