Reasons for the ‘Cannot Group That Selection’ Error
- Blank cells among numbers or dates
- Texts with numbers or dates
- Invalid format of numbers or dates
How to Use Excel VBA to Detect Causes of the ‘Cannot Group That Selection’ Error
The sample dataset showcases a Product Order Record.
You want to group all Ship Dates, but the column contains different date formats and invalid dates.
Create a user-defined function to detect all cell types in the Ship Date column:
- Press ALT + F11 to open the VBA Editor.
- Go to Insert ➤ Module to create a new module.
- Enter 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 code creates a user-defined function called ValueType. If you insert any cell address in the function, it will detect its cell type.
- Create an additional column: Data Type.
- Enter the following formula in G5.
=@ValueType(F5)- Press ENTER.
This formula returns the cell type of F5.
- Drag down the Fill Handle to see the result in the rest of the cells.
There are 3 types of cell formats in the Ship Date column:
- Date
- Blank
- Text
Convert all Blank and Text formats to Date.
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
In the Ship Date column, the red marked dates have an invalid date format.
- Select a date.
- Right-click it.
- Go to Group.
Excel shows the ‘Cannot group that selection’ error.
There are invalid date formats.
Correct the invalid date formats. Use the format Month-Day-Year.
- Group the dates.
No error occurs.
In the Grouping dialog box, select Months and click OK.
Dates are grouped by Months.
2. Blank Cells in the Dataset Cause the ‘Cannot Group That Selection’ Error in an Excel Pivot Table
In the Ship Date column, there are blank cells.
- Right-click a date in the Pivot Table.
- Click Group.
Excel shows the ‘Cannot group that selection’ error.
- Enter dates in the blank cells.
- Group the dates.
No error occurs.
- In the Grouping dialog box, select Months and click OK.
Dates are grouped by Months.
Read more: Pivot Table Custom Grouping
Practice Section
Practice here.
Download Practice Workbook
Download the Excel file.
Related Articles
- How to Group Columns in Excel Pivot Table
- How to Group Numbers in Excel Pivot Table
- 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
Get FREE Advanced Excel Exercises with Solutions!
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