Grouping dates by months, quarters, and years—or aggregating numbers into custom numeric brackets—is one of the most powerful analytical capabilities of Pivot Tables in Microsoft Excel. However, many users encounter an abrupt halt when they right-click a date or numeric column in their Pivot Table, select “Group…”, and are met with a stubborn dialogue box stating: “Cannot group that selection.” Excel provides no explanation, leaving you wondering why an identical grouping worked effortlessly in previous spreadsheets. When an Excel Pivot Table refuses to group data, it is almost never a software bug; rather, it indicates hidden blank cells, text strings disguised as dates, or invalid data types sitting within your source dataset.
Identify and Remove Blank Cells in the Source Data
Excel’s Pivot Table grouping algorithm requires 100% data type consistency across the entire column. If your source data contains even a single blank or empty row in the date column, the grouping engine cannot determine boundaries and aborts with the error.
- Navigate to your raw source data table (not the Pivot Table).
- Click the auto-filter drop-down arrow at the top of your date or numeric column.
- Scroll down to the bottom of the filter list and look for (Blanks).
- If (Blanks) is present, untick “Select All” and tick only (Blanks) to isolate the empty rows.
- Either populate these blank cells with accurate date records or delete the blank rows entirely.
- Clear the filter to show all rows, switch back to your Pivot Table, right-click inside the table, and select Refresh.
- Right-click your date column and select Group…; the date grouping window will appear immediately.
Eliminating blank cells ensures the grouping algorithm has an unbroken sequence of values to evaluate.
Convert “Text Dates” to Legitimate Excel Serial Dates
Data imported from web applications, accounting tools, or CSV exports often imports dates as plain text strings (e.g. '2026-05-12 or text-formatted dates). While they look like dates to human eyes, Excel treats them as raw text.
- In your source data table, highlight the entire date column.
- Click on the Data tab on the Excel ribbon and click on Text to Columns.
- In Step 1 of the wizard, choose Delimited and click Next.
- In Step 2, uncheck all delimiters and click Next.
- In Step 3, under Column data format, select the radio button for Date, and choose the matching format from the drop-down (such as DMY or MDY depending on your regional format).
- Click Finish.
- Return to your Pivot Table, click Refresh, and retry grouping.
Running Text to Columns forces Excel to parse each text string into a true serial date number.
Remove Text Values from Numeric Range Columns
Similarly, if you are grouping numeric values (such as ages or sales quantities) into buckets of 10 or 100, having text entries like “N/A”, “Pending”, or “Zero” within that column halts grouping.
- Inspect your numeric source column using the filter drop-down.
- Search for any non-numeric entries, placeholders, or formulas returning empty text strings (e.g.,
""). - Replace text placeholders with actual numerical values (such as
0) or remove the records. - Refresh your Pivot Table and apply grouping.
Keeping columns purely numeric allows Excel to calculate equal bin intervals without encountering syntax mismatch errors.
Disable the “Data Model” Option During Pivot Table Creation
If you create a Pivot Table and tick the checkbox labelled “Add this data to the Data Model” (Power Pivot), classic date and numeric grouping functions behave differently or are greyed out, as the Data Model requires DAX calculated columns or explicit date tables for hierarchy grouping.
- If you do not require multi-table relational modelling, create a standard Pivot Table: select your source range > Insert > PivotTable.
- Ensure the box Add this data to the Data Model is unchecked.
- Click OK.
Standard Pivot Tables offer full native one-click date grouping across seconds, minutes, hours, days, months, quarters, and years.