Fix 'Cannot group that selection' in Excel PivotTable (Dates & Numbers)

The Error

You right-click a date or number field in your PivotTable, choose Group, and Excel immediately throws:

Cannot group that selection

No stack trace. No line number. Just a dead-end dialog that tells you absolutely nothing. The culprit is almost always data quality — blank cells, dates stored as text, or mixed types lurking somewhere in your source column.

Why This Happens

PivotTable grouping is strict: every cell in the target column must be the same valid data type. One exception kills the whole operation. Grouping breaks when:

Blank cells exist anywhere in the date/number column — a single empty cell in 50,000 rows is enough
Dates stored as text — the cell looks like a date, but Excel treats it as a string
Mixed types — some cells are proper dates, others are text or raw numbers in the same column
Error values like #N/A or #VALUE! anywhere in the field

Blank cells are the most common culprit. A single empty cell in a 10,000-row column will trigger this error every time.

Quick Diagnosis

Before reaching for a fix, nail down which problem you actually have.

Check for blank cells

Select your date/number column in the source data, then press Ctrl+G → Special → Blanks → OK. Any highlighted cells mean blanks are present.

Check for text-formatted dates

Click a cell that looks like a date and check two things:

Alignment — text aligns left, real dates align right
Run =ISNUMBER(A2) in a helper column. A result of FALSE for a date cell means it's stored as text, not a real date serial number.

Check for error values

Hit Ctrl+F and search for #N/A or #VALUE! in the column. Formula errors hide easily in large datasets.

Fix 1: Remove or Fill Blank Cells

Nine times out of ten, this is all you need.

Option A — Fill blanks with a placeholder:

Select the entire date/number column in your source data
Press Ctrl+G → Special → Blanks → OK
Type a value (e.g. 0 for numbers, or 1/1/1900 as a sentinel date) and press Ctrl+Enter

Option B — Delete rows with blank dates:

Filter the column for blanks
Select all filtered rows
Right-click → Delete Row
Clear the filter

After filling or removing blanks, right-click your PivotTable and hit Refresh, then try grouping again.

Fix 2: Convert Text Dates to Real Dates

Blanks aren't the problem? Check your dates. If they're stored as text, grouping will fail even with a perfectly clean column.

Method A — DATEVALUE formula

Add a helper column and convert text to real date serial numbers:

=DATEVALUE(A2)

Copy the helper column, paste as Values Only over the original, then format the column as Date.

Method B — Text to Columns

Select the text-date column
Go to Data → Text to Columns
Click through to Step 3, choose Date as Column Data Format, pick your date order (DMY / MDY / YMD)
Click Finish

Excel converts the text strings to proper date serial numbers in-place — no helper column required.

Method C — Power Query (best for recurring imports)

// In Power Query M:
= Table.TransformColumnTypes(Source, {"OrderDate", type date})

Importing from CSV or a database every week? Set the column type to Date in Power Query once. The problem won't come back.

Fix 3: Handle Mixed Data Types with a Helper Column

Some columns are genuinely messy — dates in some rows, text labels like "N/A" or "Pending" in others. You can't group that directly. Build a clean helper column instead:

=IF(ISNUMBER(A2), A2, "")

For date columns specifically:

=IFERROR(DATEVALUE(TEXT(A2,"YYYY-MM-DD")), "")

Point your PivotTable at the helper column instead of the original. Blanks are controlled, types are consistent, grouping works.

Fix 4: Wrap Formula Errors with IFERROR

Error values like #N/A or #VALUE! in the grouping field break things just as badly as blanks. Find the source formula and wrap it:

// Before
=VLOOKUP(B2, LookupTable, 3, 0)

// After
=IFERROR(VLOOKUP(B2, LookupTable, 3, 0), "")

Replace errors with empty strings or 0, then refresh the PivotTable.

Fix 5: Use an Excel Table as the PivotTable Source

Still hitting the wall? If your source data isn't already an Excel Table, convert it with Ctrl+T. Tables auto-expand when you add rows, and the filter dropdowns on every column make data type inconsistencies obvious at a glance.

Recreate the PivotTable using the Table name as the source — not a fixed range like A1:D500. This also eliminates the "PivotTable doesn't know the data grew" problem.

Verification

Once you've applied a fix, confirm it actually worked:

Right-click anywhere in the PivotTable → Refresh
Right-click the date/number field label in the PivotTable
Click Group — the grouping dialog should open (Days/Months/Quarters/Years for dates, or a numeric interval for numbers)
Set your grouping and click OK

Still failing? Run this in any empty cell:

=COUNTA(A:A) - COUNT(A:A)

A result greater than 0 means non-numeric or non-date values are still hiding in the column. Keep hunting.

Prevention

Stop bad data at the door. For external imports, run them through Power Query with explicit type casting — it catches type mismatches before they reach your sheet. For manual entry, set Data Validation on the column to allow only dates or whole numbers. Excel rejects anything else on input, so your PivotTable grouping stays clean by default.