ExcelInsider Team
Grouping dates by month, quarter, or year in an Excel Pivot Table is used for time-based analysis. However, there are times when you need to switch back to view daily transactions by ungrouping dates. Ungrouping dates in a PivotTable is a simple process that instantly returns your data to its original, detailed format. In this article, we will guide you through three effective methods for ungrouping dates in an Excel Pivot Table.
To ungroup dates in Excel Pivot Table, here is one simple solution by using the Ungroup feature.
➤ Right-click on any date cell (e.g., a month or year) in the Pivot Table.
➤ Select Ungroup from the context menu to ungroup dates and return to the original date format.
Using Ungroup Feature to Ungroup Dates
The most common way to remove date grouping is by using the Ungroup command, which is accessible via the right-click context menu.
For this example, we will start with a sample dataset of some transactions. Here, we will create the Pivot Table and then apply the ungrouping feature.
First, let’s create a Table from the data.
➤ Select your dataset (A1:C11).
➤ Go to the Insert tab and click Table.
➤ Checkmark My table has headers and click OK.
Next, to insert the Pivot Table.
➤ Go to the Insert tab, click PivotTable.
➤ Select New Worksheet and click OK.
➤ Drag the Date field into the Rows area.
➤ Drag the Amount field into the Values area.
The Pivot Table will now show the summarized sales data, grouped by Year (e.g., 2023, 2024) and the corresponding Months.
To get to daily dates, we will use the Ungroup command on one of the grouped fields.
➤ Right-click on any grouped date cell (for example, the month “Jan” or the year “2023”).
➤ From the context menu that appears, select Ungroup.
The grouped date fields (Years and Months) will be instantly removed from the Pivot Table. The Date field in the Rows area will now display every individual date from the source data.
Applying Keyboard Shortcut
In modern versions of Excel, when you drag the Date field into the Rows area, the grouping process often happens automatically, adding the Years, Quarters, and Months hierarchy. If you simply want to undo this automatic grouping as soon as it happens, the undo keyboard shortcut is the fastest solution.
Start by building your Pivot Table and dragging the Date field.
➤ Drag the Date field to the Rows area.
➤ Drag the Amount field to the Values area.
Excel will automatically group the dates and show the summarized data fields (Years, Quarters, Months, etc.) in the Pivot Table.
To ungroup the dates and revert to the original daily transactions.
➤ Press the Ctrl + Z  keys simultaneously (the keyboard shortcut for Undo).
As a result, Excel will undo the automatic grouping action, and your Pivot Table will display the original daily dates once again.
Disabling Automatic Grouping Feature
If you want to ungroup dates automatically every time you create a new Pivot Table, you can disable the automatic grouping feature. This setting tells Excel not to create the date hierarchy when you drag a Date field into the Pivot Table.
➤ Click the File tab on the ribbon.
➤ Select Options from the bottom of the left-hand pane.
In the Excel Options dialog box:
➤ Select the Data category from the left sidebar.
➤ Scroll down to the bottom right and checkmark the box for Disable automatic grouping of Date/Time columns in PivotTables.
➤ Click OK to save the new setting.
Now, let’s create a new Pivot Table and drag the Date field into the Rows area.
➤ Drag the Date field to the Rows area.
➤ Drag the Amount field to the Values area.
Finally, the Pivot Table will now list all the dates without grouping them into Years or Months.
Frequently Asked Questions
Can I keep dates grouped by month but remove years?
Yes. When you right-click and select Group, you can manually select only Months while unchecking Years (or other levels). If you want to completely undo grouping, use Ungroup.
Why is the Ungroup option greyed out in my Pivot Table?
This usually happens when you don’t have a date field selected or your data contains blank cells or text values instead of valid dates. Sometimes, this is due to working with a calculated field instead of a raw date field.
Can I ungroup dates in an OLAP-based Pivot Table (from Power Pivot or external data)?
No. In OLAP-based Pivot Tables, Excel may not allow ungrouping dates because the grouping is handled by the data source.
Concluding Words
Above, we have explored several methods to ungroup dates in Excel Pivot Table. The Ungroup feature is best for fixing an existing grouped date in Pivot Table, while Ctrl + Z Â is an immediate fix for newly created dates. For long-term preference, disabling the automatic grouping option ensures the dates will not be grouped. If you have any questions, please don’t hesitate to share them in the comments section below.
