Forum Discussion

Marcus_Booth's avatar
Marcus_Booth
Brass Contributor
Apr 04, 2025

No access to group/ungroup dates in Pivot Table filter. Jan-24 and Jan-25 show as Jan only.

I'm trying to follow instructions I found for grouping/ungrouping dates on a Pivot Table filter. 

Using 365 (desktop app and web based housed on Sharepoint Cloud)

The dates on my column are formatted as Date='Mar-12', so when I type in Jan25, it converts to 1/25/2025 but displays Jan-25. The day of the week doesn't matter, just Month and Year.

Adding that column to the Pivot table as filter, shows...

<12/1/23
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
>3/2/2025

I have data for Jan-24 and Jan-25, but they are lumped together. 

There is no option to right-click on the dates displayed within the Pivot Table Filter. What am I missing? 

Thanks! 

excel
Excel for web
  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Apr 05, 2025

    Any chance to fix by the following:

     

    1. Check Date Formatting: Ensure that the column with dates is formatted consistently as "Date" in Excel. If the format is inconsistent, Excel might not recognize the dates properly for grouping.
    2. Disable Automatic Grouping: In Excel 365, you can disable automatic grouping of dates in Pivot Tables:
      • Go to File > Options > Data.
      • Under "Data options," check the box for "Disable automatic grouping of Date/Time columns in PivotTables."
      • This setting prevents Excel from automatically grouping dates into months, quarters, or years.
    3. Manual Grouping: If you want to group dates manually:
      • Select the date field in the Pivot Table.
      • Go to PivotTable Tools > Analyze > Group > Group Selection.
      • In the dialog box, choose the grouping options (e.g., Months and Years).
    4. Ungrouping Dates: If dates are already grouped and you want to ungroup them:
      • Right-click on the grouped date field in the Pivot Table.
      • Select "Ungroup."
    5. Filter Settings: If you're using the date field as a filter, Excel might display grouped dates by default. You can try adding the date field to the Rows or Columns area instead, where grouping options are more flexible.

Resources