Excel Cannot Group Dates? Fix the PivotTable Error

Errors & fixes · Updated November 19, 2026

In Excel for Microsoft 365 on Windows, the PivotTable message “Cannot group that selection” usually means the date field contains a blank, text value, or error instead of a valid date. Check the entire source column, correct or separate the offending entries, refresh the PivotTable, then right-click a date and choose Group.

What the grouping error means

Grouping lets a PivotTable combine individual dates into useful periods such as months, quarters, or years. For example, a list of daily orders can become a monthly sales summary without changing the source records. Excel needs the field to contain consistently recognizable dates to create those groups. If even one source entry is blank, stored as text, or an error, Excel may not be able to treat the whole field as a date sequence.

The message is about the values Excel is trying to group, not necessarily the cell you clicked. A date column can look uniform because its cells share the same display format, while its underlying values differ. Formatting text to look like a date does not convert that text into a true Excel date.

Why Excel dates can look valid but fail

Excel stores ordinary dates as serial numbers and applies a date format to display them. A cell showing 4/7/2025 might therefore contain a genuine date, while a neighboring cell showing 4/8/2025 could contain text imported from another system. Those values look similar on screen but are different to Excel.

Common causes include:

  • Blank cells: The date field may have an empty row within the source range, including a formula that displays an empty string.
  • Text dates: Imported dates may be text, especially when the source uses a different date order or regional convention.
  • Errors or other values: A cell containing an error, a label such as “Pending,” or an unexpected number can break a consistent date field.
  • Incomplete cleanup: The PivotTable source may include extra rows or columns beyond the visible data, including blank rows.
  • Unsupported PivotTable source: A PivotTable built from an OLAP source or the Excel Data Model may not support the usual manual grouping command.

Do not assume that changing the number format fixes the problem. A format changes how a value appears; it does not turn arbitrary text into a date.

Find the problem in the source column

Start with the original source data rather than trying to repair the grouped PivotTable. Identify the date field used in the Rows or Columns area, then inspect every record included in the PivotTable’s source range. Sorting the column can bring blanks and unusual text together, but check carefully: sorting may put text dates in a separate section rather than in chronological order.

For a quick test, with the first source date in A2, enter =ISNUMBER(A2) in a temporary helper column and fill it down. Excel dates are numeric serial values, so TRUE is consistent with a real date; FALSE indicates that the entry is not stored as a number. This is a useful flag, not a complete date validator, because an ordinary number also returns TRUE. Review any flagged values in the context of the column.

To check for empty cells, with the date field in A2:A100, enter =COUNTBLANK(A2:A100). The result is the number of blank cells in that range; COUNTBLANK also counts formulas that return an empty string. Adjust the range to cover all source records. If the source is an Excel Table, check its full date column, not just the rows currently visible in a filtered view.

Fix blanks, text dates, and invalid entries

Choose a correction that preserves the meaning of the data. If a date is genuinely unknown, do not fill in a made-up date just to make grouping work. Instead, decide whether those records should be excluded from this analysis, reported separately, or assigned a legitimate date under your organization’s data rules. Keep the original records available where they are needed.

For text dates, first confirm their intended order. For example, 03/04/2025 can mean March 4 or April 3 depending on the source convention. Then convert the values using a method suited to the data:

  • Text to Columns: Select the affected cells, choose Data > Text to Columns, select Delimited, and choose Next twice. Under Column data format, choose Date and the correct order, such as DMY or MDY, then choose Finish. Check the results before refreshing the PivotTable.
  • Power Query: When importing data, set the date column’s type deliberately. If the source’s locale differs from the computer’s settings, use the option to change type with locale so Excel interprets day and month correctly.
  • Source-system correction: If the date format or missing values originate in an exported file, correcting the export or import settings can prevent the same issue from returning.

Resolve errors and non-date labels separately. If a field mixes dates with statuses such as “Not scheduled,” consider keeping status in another column. A field intended for date grouping should represent dates consistently.

Refresh the PivotTable and try grouping again

After fixing the source, click inside the PivotTable and choose PivotTable Analyze > Refresh. If the PivotTable uses an older fixed range, make sure the corrected rows are included; use PivotTable Analyze > Change Data Source if the range needs updating. An Excel Table is often a practical source because it expands as rows are added, though you should still confirm that the PivotTable includes the intended records.

  1. Click one date item in the PivotTable’s date field. Avoid selecting the entire worksheet or unrelated fields.
  2. Right-click the selected date and choose Group. Alternatively, use PivotTable Analyze > Group > Group Field when that command is available.
  3. In the Grouping dialog, select the periods you need, such as Months and Years, then choose OK. For daily buckets, select Days and set the number of days per group.

If you only select Months, Excel can combine the same month across different years. For instance, January 2024 and January 2025 may appear in one January group. Select both Years and Months when the report needs a separate January for each year. The available choices can depend on the source field and PivotTable type.

When the Group command is unavailable

If Group or Group Field is disabled, first confirm that you clicked a date item inside the PivotTable and that the date field is part of the PivotTable layout. Then check how the PivotTable was created. The standard manual grouping feature is not available in the same way for some OLAP and Data Model PivotTables. Cleaning the source alone will not enable a feature the PivotTable source does not support.

For a Data Model report, a common alternative is to add date attributes—such as year, quarter, and month—to a calendar table, relate that table to the transaction data, and use those attributes in the PivotTable. For an OLAP connection, the model or cube may need to provide the required date hierarchy. These approaches make the reporting structure explicit rather than relying on manual grouping.

Also check whether the field is actually a date field rather than a text label that happens to contain month names. If the source data is maintained outside Excel, changing its type in the source or query may be necessary. The exact remedy depends on the connection and model design.

Prevent the error in future reports

Consistent source data is more reliable than repeatedly fixing individual PivotTables. Keep one date per record in a dedicated date column, use genuine Excel dates, and keep notes or status labels in separate fields. When importing data, verify the interpretation of day and month before building a report, particularly when files come from different countries or systems.

Before distributing a workbook, refresh the PivotTable and check that its source includes all records. If a workbook’s PivotTable retains old items after source changes, its PivotTable Options include a setting for how many items to retain per field. Clearing retained items can tidy old labels, but it does not repair blank or text values in the current source. Always fix the underlying data first.

For a recurring monthly report, document how missing dates are handled and use the same rule each time. That avoids silently assigning false dates and makes totals easier to interpret. On Mac, PivotTable controls and their placement can differ slightly from the Windows ribbon; use the PivotTable contextual tab or the field’s right-click menu. Excel for the web may also have different editing capabilities, so open the workbook in the desktop app if the required grouping command is unavailable.

Frequently Asked Questions

Why does Excel say it cannot group dates when all the cells look like dates?
Some entries may be text dates, blanks, or errors even if they look similar to valid dates. Test the underlying values and inspect the complete source range; changing the cells’ display format alone does not convert text to dates.

Does a blank row cause the PivotTable date grouping error?
It can, if the blank is inside the source data included by the PivotTable. Check the entire date field, including formulas that display nothing, and decide how genuinely missing dates should be handled rather than inventing replacements.

How do I group dates by month and year separately?
After choosing Group on a date item, select both Months and Years in the Grouping dialog. Selecting Months alone can combine matching months from different years.

Why is Group greyed out in my PivotTable?
Confirm that a date item inside the PivotTable is selected. If the command remains unavailable, the PivotTable may use an OLAP or Data Model source that does not support ordinary manual grouping; use date fields or a calendar table supplied by the model.

Will refreshing the PivotTable fix the error by itself?
No. Refresh updates the PivotTable from its source, but it does not convert text dates, remove errors, or decide what to do with missing dates. Correct the source first, then refresh and try grouping again.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.