Dates and grouping Updated November 2026

Dates not grouping

Excel refuses to group when any cell in the date column is not a true date: text that looks like a date, a blank, or a note like TBD. Convert the column with Data, Text to Columns (Finish) or DATEVALUE, remove blanks, then Refresh and group again.

Syntax

Select the date column > Data > Text to Columns > Finish > Refresh pivot > right-click > Group

Part	What it is
`Text to Columns`	Fastest way to coerce text dates to real dates in place
`DATEVALUE`	Formula conversion when the text uses a recognizable format
`ISTEXT / ISNUMBER`	Helper check to find the offending cells

Examples

=ISTEXT(A2) filled down the date column, then filter for TRUE

Result: Shows every row whose date is text; real dates return FALSE.

Select A2:A500 > Data > Text to Columns > Delimited > Next > Next > Column data format: Date (MDY) > Finish

Result: Text dates become serial dates aligned right; the pivot groups after a Refresh.

Row dropdown on the date field > untick (blank) > Group

Result: Grouping succeeds because blanks are excluded; filling the source is the permanent fix.

Common mistakes

The dates look fine but are left-aligned: left-aligned dates are text; right-aligned are real.
Grouping works for one pivot but not another on the same data: the second pivot was built before the fix, so refresh it.
The source range extends past the data: trailing empty rows count as blanks; shrink the range or use a Table.
The dates were pasted from a web page: they may contain non-breaking spaces, so use CLEAN and TRIM or Find and Replace on the space character.

Practice Dates not grouping

These drills check your answer on live data, free, no account needed.

Revenue by monthShow total Revenue for each month of 2025.
Ticket volume per monthCount tickets opened per month, with Channel across the top.

Questions

Why does my pivot table say Cannot group that selection?

At least one cell in the field is not the same type as the others: a text date, a blank or a number in a date column. Fix the source, refresh, try again.

Why does the pivot show dates as individual days instead of months?

Automatic grouping is off or was undone. Right-click a date and choose Group, Months.

Group dates by month, quarter and yearGroup a date field in a pivot table by months, quarters and years with right-click, Group, and get year by month breakdowns. Auto grouping, ungrouping, weeks.
Refresh a pivot tableRefresh a pivot table after the source changes with right-click Refresh or Alt+F5, refresh every pivot with Ctrl+Alt+F5, and set the file to refresh on open.
Blank cells in a pivot tableReplace empty pivot cells with 0 or a dash in PivotTable Options, and get rid of the (blank) row label by filtering it out or filling gaps in the source data.