The Pivot Table Date Collapse
If you create a Pivot Table in modern versions of Microsoft Excel and drag a column containing daily dates (e.g., “Jan 1, 2023”, “Jan 2, 2023”) into the Rows area, Excel will automatically intervene. Instead of showing you the individual days, Excel instantly collapses and groups the data into “Months,” “Quarters,” and “Years.” While this is helpful for high-level financial summaries, it completely ruins your table if you are trying to analyze daily sales metrics or specific day-of-the-week trends, forcing you to manually ungroup the data every single time you build a report.
How to Disable Automatic Date Grouping
You can turn off this aggressive feature deep within Excel’s advanced data settings, forcing Pivot Tables to always display raw, granular dates exactly as they appear in your source data.
1. Open Microsoft Excel.
2. Click on the File tab in the top-left corner of the ribbon.
3. Select Options at the bottom of the left-hand green sidebar.
4. In the Excel Options window, click on Data in the left menu.
5. Look in the section labelled “Data options.”
6. Locate the checkbox labelled Disable automatic grouping of Date/Time columns in PivotTables.
7. Tick the checkbox so a checkmark appears.
8. Click OK to save your changes.
How to Manually Ungroup Existing Tables
Changing this setting only applies to Pivot Tables you create in the future. It will not fix a table that Excel has already grouped. To fix an existing table, right-click on any of the grouped date headers (such as the word “Q1” or “January”) directly inside the Pivot Table. From the context menu that appears, select Ungroup. The hierarchy will instantly disappear, expanding your table to show every individual daily date record.