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