Excel Cannot Group Dates in Pivot Table: Every Fix

You drag a date field into a pivot table, right-click, choose Group, and Excel throws up "Cannot group that selection". Or the Group Field button on the ribbon is simply greyed out. Here is why that happens and how to fix every version of it.

Last updated: August 24, 2026

What this guide covers

• What "Cannot group that selection" actually means
• The six reasons Excel cannot group dates in a pivot table
• How to find the bad cells (three methods)
• The Excel setting that switches date grouping off
• The Data Model case
• How to group pivot table dates by month, quarter or year
• Troubleshooting table
• Frequently asked questions

Date grouping lets you roll a long list of transactions up into months, quarters or years without touching the source data. It is exactly how an expense report with pivot consolidation summarizes monthly travel costs. When it breaks, it breaks for a reason, and the reason is almost always in the data rather than in the pivot table. People hit this as pivot table not grouping dates, as unable to group dates in pivot table, or simply as why can't I group dates in pivot table. It is one fault with several names.

Suggested read: How to Connect Slicers to Multiple Pivot Tables

What does "Cannot group that selection" mean in a pivot table?

It means Excel found at least one item in the field you are trying to group that it cannot treat as a date. Grouping is all-or-nothing: the field either contains dates from top to bottom, or grouping is refused for the whole field.

The rule: one bad cell in ten thousand is enough. Excel does not tell you which cell it is, and it does not group the good ones and skip the rest.

The exact wording is "Excel cannot group that selection", and you will see the same thing described as pivot table cannot group that selection. There is also a quieter version: the pivot table date filter not grouping into its usual collapsible year and month tree. If the filter dropdown gives you a flat list of individual dates instead of years you can expand, that is the same diagnosis. The field is not being read as dates.

You will meet the problem in one of two shapes. Either you right-click and get the "Cannot group that selection" message box, or the Group Field button on the PivotTable Analyze tab is greyed out before you even try. Both point at the same underlying cause list.

The six reasons Excel cannot group dates in a pivot table

The first four are all the same problem wearing different hats: something in the column is not a date. The last two are settings problems, and they are the ones most articles never mention.

How to find the cells that are breaking it

Excel will not point at the offending cell, so you have to go looking. Three ways, quickest first.

Method 1: use a filter to surface text and errors

Select every cell in the data range, then click Filter on the Data tab.

Click the small arrow next to your date heading, in this example Ship Date. Excel groups real dates into a collapsible year and month tree at the top of the list. Anything that is not a date is dumped as a flat item at the bottom, below the tree. That bottom section is your problem list.

Dates typed in a format Excel does not recognise for your region are the usual culprit. Excel never converted them, so they are sitting there as text.

To isolate them, untick Select All to clear every checkbox, then tick only the text and error items at the bottom. Hit OK, and the column will now show only the bad values.

Work through them and fix them. These values are not being read as dates, which is exactly why grouping fails. Once they are corrected, click Filter again to clear it and the full range comes back with every date now recognised.

Suggested Read: How To Fix a Reference Isn't Valid Error in Excel.

Method 2: Go To Special

Go To Special selects cells by what they contain, which makes it good at flushing out text hiding in a number column.

1. Click any cell in the date column and press Ctrl+Space to select the whole column.
2. On the Home tab, choose Find & Select, then Go To Special.
3. Choose Constants, then untick Numbers and leave Text, Logicals and Errors ticked. Click OK.
4. Everything still selected is text, a logical value or an error. Give them a fill colour so you can find them again, then fix them.

Real dates are stored as numbers underneath, which is why unticking Numbers leaves only the offenders behind. One limitation worth knowing: Go To Special > Constants ignores cells containing formulas, so if your date column is formula-driven, use the filter method instead.

Method 3: check for blanks

Same dialog, different option. Select the column, open Go To Special, choose Blanks and click OK. Every empty cell in the date column is now selected.

The rule: blank cells break date grouping just as surely as text does. A pivot field showing a "(blank)" item will not group.

Either fill the blanks with real dates, or narrow the pivot table's source range so those rows are excluded. Changing the source to an Excel Table (Ctrl+T) prevents the related problem of trailing empty rows creeping into the range.

Once the column is clean, refresh the pivot table with Alt+F5, or right-click it and choose Refresh. Grouping will not start working until you refresh.

Suggested read: Excel Pivot Table Training

The Excel setting that switches date grouping off

If your dates are clean and grouping still will not happen automatically, check this before you go any further. Excel has an option that disables date grouping outright, and someone may have ticked it on your machine or in your organisation's template.

1. Go to File > Options.
2. In the list on the left, click the Data category.
3. At the end of the Data options section, look for Disable automatic grouping of Date/Time columns in PivotTables.
4. Untick it, click OK, and rebuild the pivot table.

Two things to know. The setting only affects the automatic grouping that happens when you drop a date field into the Rows area, and it takes effect on pivot tables you create afterwards, so an existing one needs recreating.

Microsoft documents this checkbox under File > Options > Data for Microsoft 365, Excel 2021 and Excel 2024. If your version of Excel shows no Data category in the Options dialog, the checkbox is not available in that build. Do not confuse it with Group dates in the AutoFilter menu, which does sit on the Advanced page but controls the AutoFilter dropdown rather than pivot tables.

Pivot tables built on the Data Model

When you insert a pivot table, there is a checkbox at the bottom of the dialog labelled Add this data to the Data Model. If that was ticked, you are working with a Data Model pivot table, and date grouping behaves differently. The Group command is frequently unavailable, and no amount of cleaning the source column will bring it back.

You have two ways out:

• Rebuild without the Data Model. Insert a fresh pivot table from the same range with that checkbox cleared. Normal grouping returns.
• Add a proper date table. If you need the Data Model, the correct pattern is a separate calendar table with Year, Quarter and Month columns, related to your data on the date field. You then use those columns as your grouping levels instead of relying on the Group command.

The rule: if Group is greyed out on a pivot table whose source column is provably clean, check whether it is a Data Model pivot table before you do anything else.

How to group pivot table dates by month, quarter or year

With the column clean, how to group dates in pivot table becomes a two-click job. One dialog handles pivot table group by month, by quarter and by year, and the Excel pivot table group by month result is what most people are here for.

1. Build the pivot table. On the Insert tab, click PivotTable, choose your source range and where you want the table to go.

   

2. Drag the date field into the Rows area.
3. Right-click any date in the pivot table, not a value cell, and choose Group. You can also use Group Field on the PivotTable Analyze tab.
4. The Grouping dialog opens with a list: Seconds, Minutes, Hours, Days, Months, Quarters, Years. Click the levels you want. They are multi-select.
5. Click OK.

The rule: to make a pivot table group dates by month and year together, select both Months and Years in the same dialog. Picking Months alone lumps January 2025 and January 2026 into one row.

That last point catches people out constantly, and it is the reason a report can show twelve rows when it should show twenty-four. If you want a running monthly timeline, tick Years and Months together and Excel nests them.

Other useful combinations from the same dialog:

• Group dates by year only: tick Years and nothing else.
• Group dates by quarter: tick Quarters, and add Years if the data spans more than one.
• Group dates by week: tick Days and set "Number of days" to 7. Weeks are not on the list, this is how you get them.
• Ungroup: right-click the grouped field and choose Ungroup.
• Numbers, not dates: the same dialog handles numeric fields, so how to group numbers in pivot table has the same answer. Drop a numeric field into Rows, right-click, choose Group, then set a starting value, an ending value and an interval.

Microsoft's own Group or ungroup data in a PivotTable page documents the same dialog if you want the reference.

Troubleshooting: pivot table dates not grouping

Excel pivot table date grouping not working after all of that, or the pivot table won't group dates no matter what you try? Match your symptom below.

Pivot table grouping: summary and key takeaways

Date grouping fails for a small number of reasons and almost all of them live in the source column rather than in the pivot table. Get every cell in that column holding a genuine date, with no blanks, no text and no errors, refresh, and grouping works. If it still does not, the answer is one of the two settings above rather than anything in your data.

Pivot-grouped dates also feed neatly into a gantt chart timeline template when you need to visualize phases by month or quarter. Once grouping is behaving, the next thing worth learning is how to connect a slicer to multiple pivot tables, so one date filter drives every report on the sheet.

Frequently asked questions about pivot table date grouping

Why can't I group dates in a pivot table?

Because at least one cell in the date field is not a real date. Text entries, error values and blank cells all block grouping, and one is enough to stop the whole field. Clean the column, refresh the pivot table with Alt+F5, and try again.

What does "Cannot group that selection" mean?

It is Excel telling you the field you selected contains something it cannot treat as a date or a number. It does not say which cell, and it will not group the valid entries and skip the rest. The whole field has to be clean.

Why is the Group Field button greyed out?

Usually one of three things. You clicked a value cell instead of a date in the Rows area, the pivot table is built on the Data Model, or the worksheet is protected. Click a date first, then check whether "Add this data to the Data Model" was ticked when the pivot table was created.

How do I group pivot table dates by month and year?

Right-click any date in the pivot table, choose Group, and in the Grouping dialog tick both Months and Years, then click OK. Ticking Months on its own merges the same month from different years into a single row.

Can blank cells stop a pivot table grouping dates?

Yes. A blank in the date column shows up as a "(blank)" item in the field, and Excel will not group a field containing it. Fill the blanks with real dates or shrink the pivot table's source range so those rows are left out.

How do I group pivot table dates by week?

There is no Weeks option in the Grouping dialog. Instead, tick Days and set the "Number of days" box to 7. The starting date of the range decides which day your weeks begin on, so set it to a Monday if you want Monday-to-Sunday weeks.

Do I need to refresh the pivot table after fixing the data?

Yes. A pivot table reads from a cached copy of the source data, so corrections you make in the sheet do not reach it until you refresh. Press Alt+F5, or right-click the pivot table and choose Refresh.

Related Articles:

How to Connect a Slicer to Multiple Pivot Tables in Excel

Excel: Remove Trailing Spaces Quickly and Easily With These Simple Steps

Excel Repeat Last Action: What Is It and How Does It Work?

SUM Index-Match: What Is It, and How Does It Work?