To ungroup dates in an Excel PivotTable, click a grouped date label, then choose PivotTable Analyze > Ungroup or right-click the label and choose Ungroup. Excel removes the date grouping and displays the underlying dates individually; it does not remove or alter the dates in your source data.
Quick steps to ungroup PivotTable dates
These steps apply to Excel for Microsoft 365 on Windows. First, click a label in the grouped date field—not a number in the Values area. For example, if the row labels show years or months, click one of those labels. Then use either method below:
- Ribbon: Open PivotTable Analyze and choose Ungroup in the Group section.
- Context menu: Right-click the grouped date label and choose Ungroup. If right-clicking is inconvenient, select the label and press Shift+F10 to open its context menu in Windows.
The PivotTable should switch to individual date entries. If the dates are still shown at a broad level, check that you selected a grouped date label in the correct row or column field. A PivotTable can contain more than one date field, and ungrouping one field will not necessarily change another.
On Excel for Mac, select a grouped date label and use the Ungroup command on the PivotTable Analyze tab, or Control-click the label to look for Ungroup in the context menu. Ribbon labels and layout can differ slightly by release. Excel for the web may not offer the same full grouping controls as desktop Excel, so open the workbook in the desktop app if you cannot find the command.
What date grouping does
Grouping changes how a PivotTable organizes date values. Instead of listing every transaction date, Excel can combine dates into higher-level periods such as months, quarters, or years. A sales PivotTable, for instance, might show one row for each month, with the total sales for that month. The source table still contains each transaction and its original date.
When Excel groups a date field, it can create companion fields for the chosen periods. The PivotTable Fields pane may show entries such as Years, Quarters, or Months in addition to the original date field. If you drag one of these period fields into Rows or Columns, the report displays that level of detail. Ungrouping removes the grouping structure so that the PivotTable can show the individual date values again.
This is different from changing a date’s appearance. A cell can display a date as 3/5/2026, 5-Mar, or a serial number without changing the underlying value or grouping. If the PivotTable still lists one row per month, changing the number format will not ungroup it. Use Ungroup to change the grouping; use number formatting to change how a date is displayed.
What to expect after ungrouping
After ungrouping, the PivotTable can become much longer because each distinct date may get its own row or column. If the source has thousands of transactions spread over several years, a monthly summary may occupy a dozen rows while the ungrouped report can occupy hundreds or thousands. That is normal: the report is showing finer detail, not creating extra source records.
Other parts of the PivotTable usually remain in place. For example, a Sum of Sales value field can continue to show sales totals, but those totals are now calculated for individual dates rather than months or years. If multiple source records share the same date, they may still be combined into one date row, with their values aggregated there. Ungrouping means one group per distinct date value, not necessarily one row per source record.
Dates that include times can also appear as separate values when their time portions differ. Two entries on the same calendar day—such as 9:00 a.m. and 2:30 p.m.—are distinct date-time values. If the PivotTable displays a date format that hides the times, the labels may look identical even though the underlying values differ. Consider applying a format that includes time if you need to distinguish them.
When to ungroup, regroup, or change formatting
Choose the operation that matches the report you need. Ungroup when you need to inspect individual dates or return to the original date-level view. Keep dates grouped when the purpose is to compare periods and a daily breakdown would make the report harder to read. Regroup when you want a different time scale, such as quarters instead of months.
To regroup, select a date label and choose PivotTable Analyze > Group Field, or right-click and choose Group. In the Grouping dialog, select the time units you want, such as Months, Quarters, or Years. Depending on the data and Excel version, the dialog can also offer Days and an interval for grouping by a number of days. For example, selecting Months and Years produces a monthly report organized under year headings. You can change the report’s time scale without editing the source date column.
If the only problem is that dates look like numbers, do not ungroup. Right-click a date value, choose Field Settings, then use Number Format to choose a date format for the PivotTable field. Setting the format through Field Settings is generally more reliable than formatting a few visible cells, since PivotTable updates can rearrange or replace those cells.
Common errors and how to fix them
A frequent message is Cannot group that selection. It usually means the source field contains something Excel cannot treat consistently as a date, or the selected field is not a usable date field. Check the source column for blank cells, text that looks like a date, error values, or a mix of dates and other content. A genuine Excel date is stored as a numeric date value; a text string that merely resembles a date may not be recognized for grouping.
To troubleshoot, inspect the source date column and correct the underlying entries rather than trying to fix only the PivotTable display. Confirm that dates are real date values, remove or handle blank and error entries, and make the column consistent. Then refresh the PivotTable and try grouping again if grouping is still desired. If the workbook uses an external data source or Data Model, the available grouping behavior can differ from a standard PivotTable built from a worksheet range.
If Ungroup is unavailable or dimmed, check these possibilities:
- The selected cell is not a grouped date label. Click a year, month, or other grouped item in the PivotTable body.
- The field is not grouped. A date format alone does not create a grouping that can be removed.
- The PivotTable is based on a Data Model or another source with different grouping limitations. Try the same command on a conventional PivotTable if you need to compare behavior.
- The worksheet is protected or the workbook is in a mode that restricts editing. Check whether you have permission to change the PivotTable layout.
If dates turn into serial numbers after ungrouping, the values have not necessarily been damaged. Excel stores dates as numbers and applies a date format to display them as calendar dates. Set the field’s number format to a date using Field Settings. If the source entries were text rather than true dates, however, changing the format alone will not convert that text into dates; correct the source values and refresh.
Refreshes, source data, and keeping the report stable
Ungrouping affects the PivotTable, not the source table. It does not delete rows, rewrite dates, or remove date values from the worksheet. You can verify this by checking the original date column. If the report uses a PivotTable cache, a refresh updates the report from its source, but it is still sensible to check the field layout and grouping after substantial changes to the source or workbook structure.
For fewer surprises, keep source dates in a clearly labeled column and use a consistent data type. An Excel Table is useful when the source list grows, because new rows can be included when the PivotTable source is refreshed. After adding data, choose PivotTable Analyze > Refresh to update the report. Refreshing does not mean ungrouping: these are separate actions, so use the explicit Ungroup command when you want individual dates.
Also consider saving a copy before changing a complex report. Ungrouping can make the layout much larger and may require scrolling or adjusting filters, but it is reversible: you can group the date field again. If you are unsure which fields are in use, inspect the Rows, Columns, Filters, and Values areas in the PivotTable Fields pane before changing the layout.
Alternatives when you need date detail
Ungrouping is the direct choice when the existing PivotTable should show date-level results. But it is not always the best way to answer a question about a few transactions. You could filter the PivotTable to a particular period, add a transaction identifier or descriptive field to Rows, or inspect the source table directly. Each option serves a different purpose: the PivotTable summarizes, while the source list preserves the original record-level details.
If the report needs both a summary and a way to inspect detail, keep a grouped PivotTable for the overview and use its drill-down feature on a value cell. In a standard PivotTable, double-clicking a summarized value can create a new worksheet containing the source records that contribute to that value. This is often more manageable than expanding every date in the main report. The detail sheet is an extract for inspection; it does not replace or edit the source data.
Another option is to make a separate PivotTable from the same source, leaving one grouped by month and ungrouping the other for daily analysis. That avoids repeatedly changing a presentation-ready summary. Be aware that multiple PivotTables can share a cache depending on how they were created, so changing a grouping may affect another PivotTable that shares that structure. Check both reports after a grouping change if they are related.
Frequently Asked Questions
How do I ungroup dates in an Excel PivotTable?
Click a grouped date label and choose PivotTable Analyze > Ungroup, or right-click the label and choose Ungroup. The PivotTable then shows individual date values instead of the selected date grouping.
Why can’t I ungroup dates in my PivotTable?
The field may not actually be grouped, or you may have selected a value cell rather than a grouped date label. If the command is unavailable, check that the PivotTable is editable and that its source type supports the grouping operation.
Does ungrouping dates change my original data?
No. Ungrouping changes the PivotTable’s organization, not the source date column. The source data remains as it was, unless you separately edit it.
Why does Excel say it cannot group that selection?
The source date field may contain blanks, errors, text values, or inconsistent entries. Check and correct the source column so Excel can recognize its values as dates, then refresh the PivotTable and try again.
How do I show dates instead of numbers after ungrouping?
Right-click a date value, open Field Settings, and choose a suitable format through Number Format. Excel stores dates as numbers internally, so the right format makes those values display as calendar dates.
Can I keep months but remove years?
Use Group Field to open the Grouping dialog and select the time units you want, such as Months without Years, when the source and PivotTable allow it. If the field is already grouped in an unwanted way, ungroup it first and apply the desired grouping.
Related
- Excel Merge Shortcut: How to Merge Cells in Microsoft 365
- Excel Merge Shortcut: Keys, Options, and Safer Methods
- Excel Merge Keyboard Shortcut and Safer Alternatives
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets: Count Cells That Contain Text ValuesTo count cells containing text in Google Sheets, use =COUNTIF(A2:A100,"*"), replacing A2:A100 with your range. It counts text…
- Create a Quotation Template in Word: Step by StepCreate a reusable quotation template in Microsoft Word by setting up your business details, customer fields, itemized pricing,…
- How to Use Mail Merge in Microsoft Word Step by StepTo use mail merge in Microsoft Word, prepare a recipient list, connect it to a Word document through…
- Customize Styles in Word: A Practical Step-by-Step GuideTo customize styles in Microsoft Word, open the Home tab, right-click a style in the Styles gallery, choose…
- Microsoft Excel Shortcut Keys: Essential CommandsMicrosoft Excel shortcut keys let you perform common actions from the keyboard instead of opening ribbon menus. In…
- How to Make a Monthly Budget Plan in Google SheetsTo make a monthly budget plan in Google Sheets, create columns for spending categories, planned amounts, and actual…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.