[Fixed] Excel Pivot Table: Cannot Group That Selection (2 Easy Solutions)

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.


Download Practice Workbook

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


Causes for Occurring ‘Cannot Group That Selection’ Error

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

Excel Pivot Table: Cannot Group that Selection


VBA to Detecte 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

VBA to Detecte Causes of ‘Cannot Group that Selection’ Error

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.

VBA to Detecte Causes of ‘Cannot Group that Selection’ Error

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’.


2 Solutions to Fix ‘Cannot Group That Selection’ in Excel Pivot Table

1. Correcting Invalid Data to Fix ‘Cannot Group That Selection’ Error

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

Correcting Invalid Data to Fix ‘Cannot Group that Selection’ Error

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

Excel Pivot Table: Cannot Group that Selection

Excel shows a ‘Cannot group that selection’ error.

Excel Pivot Table: Cannot Group that Selection

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.

Excel Pivot Table: Cannot Group that Selection

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

Excel Pivot Table: Cannot Group that Selection

Read More: [Fixed] Excel Pivot Table Not Grouping Dates by Month


2. Filling Blank Cells to Fix ‘Cannot Group That Selection’ Error

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.

Filling Blank Cells to Fix ‘Cannot Group that Selection’ Error

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

Excel Pivot Table: Cannot Group that Selection

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

Excel Pivot Table: Cannot Group that Selection

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.

Excel Pivot Table: Cannot Group that Selection

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

Excel Pivot Table: Cannot Group that Selection

Read More: How to Group Data in Pivot Table (3 Simple Methods) 


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.


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. And please visit our website ExcelDemy to explore more.


Related Articles

Mrinmoy Roy

Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

2 Comments
  1. I have this problem. But when I look at my data, everything is in the correct date format and there are no blanks.
    It’s a pivot table that has been in the spreadsheet for years and it only recently became unable to group. It was always grouped before.

    • Hello STEPH,
      Did you enter any newer data into your pivot table field? With newer or re-entry of data, you need to go to the PivotTable Fields window (at the right corner of the worksheet)-> unmark the corresponding field of your data (i.e. Ship Date for this article) -> right-click on the PivotTable and click Refresh-> again mark the corresponding field that you unmarked a little bit ago.
      Thanks and Regards

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo