Right-click any date in the pivot table’s Rows or Columns area and choose Group. In the By list, select Months and Years, then click OK. Selecting Years as well keeps January 2024 and January 2025 on separate rows; with Months alone, they merge into one.
Add Quarters to the selection for quarterly totals, or pick Hours, Minutes or Seconds when the field holds real date-time values. Each level you select becomes its own field that you can move, expand and collapse.
For weeks, select only Days and set Number of days to 7. Weeks count from the first date in your data, so to get Monday-to-Sunday weeks, untick Starting at and enter a Monday on or before your first date. The same Starting at and Ending at boxes set a custom range, and dates outside it fall into groups labelled with < and >.
From Excel 2016 onwards, a date field dragged into Rows is grouped automatically, usually into Years, Quarters and Months. To get the individual dates back, right-click one and choose Ungroup. To stop it happening, tick Disable automatic grouping of Date/Time columns in PivotTables under File → Options → Data.
If Excel shows Cannot group that selection., the date field contains a blank or a value stored as text. Fix those cells in the source data, for example with Data → Text to Columns for imported text dates, make sure the source range has no empty rows, then refresh the pivot table and group again.
By grouping dates in a pivot table, you can create instant reports.
Here’s the point: let’s say you want to see all the dates as months. Instead of adding a different column to your data, it’s better to group the dates.
It’s super easy.
Here’s the good news: In addition to months, you can use years, quarters, time, and even a custom date range for grouping. In this post, I’d like to show you the exact steps.
Pivot Tables are one of the INTERMEDIATE EXCEL SKILLS.
Pivot Table Group Dates By Month in Excel
Here are the detailed steps on how to group dates in a Pivot Table by month:

- Select the dates you want to group once you’re in the Pivot Table. You can do this by clicking and dragging the cursor over the cells that contain the dates.
- After selecting the dates, right-click on any of the highlighted dates. This will bring up a context menu with several options.
- In the right-click menu, look for the option “Group”. Clicking this will open a new dialog box with grouping options for your selected data.
- The dialog box that appears will have a “By” section. Here, you need to select “Months.” After making your selection, click “OK”. This will close the dialog box, and the changes will be applied to your Pivot Table.
You can also access the group option from PivotTable Analyze tab. Go to the Analyze Tab and click the “Group Selection” button to open the dialog box.
You can also use the above steps to group dates by years, quarters, and days in a pivot table.
This is like an automatic grouping of the dates; you don’t need to change the data source.
Grouping by Month is useful for tracking trends or spotting patterns over time. It allows you to see your data in a more manageable and understandable format and make informed decisions based on it.
Just keep one thing in mind: if your dates cover more than one year, select both “Months” and “Years” in the dialog box. With only “Months”, Excel puts January 2023 and January 2024 together in the same “Jan” group.
And in Excel 2016 and later, when you add a date field to the Rows or Columns area, Excel may group it on its own into Years, Quarters and Months. To stop that, go to File ➜ Options ➜ Data and tick “Disable automatic grouping of Date/Time columns in PivotTables”.
Pivot Table Group by Week (Weekly Sales Data Summary)
You can also create a group of 7 days to get a week-wise summary. Please follow the steps below to do this.
- To start, select any date in the date field. Then right-click on it and click on the Group option.
- Select the “Days” option in the By list of the dialog box (and only Days). Enter 7 in “Number of days”.

- Finally, click OK to apply the grouping of dates based on the range you have specified.
You can use the above steps to create a group of dates for any number of days.
Please note that the weeks created by the pivot table start from the “Starting at” date, not from Monday. If you want Monday-to-Sunday weeks, change the “Starting at” date to a Monday before you click OK.
Group by Hours in Pivot Tables (Hourly Sales Data Summary)
You also have the option to group dates by time.
For example, if you have dates with time and want an hourly summary, follow these simple steps.
- Select any of the cells from the date field (dates columns).
- Right-click on it and select Group.
- Select Hours in the By list.

- Click OK.
You can also use the above steps to group dates in the pivot table by minutes and seconds.
In the same way, you can group the data by minutes and seconds, as long as your data has times in it.
But make sure your dates and times are real date/time values (not text) before you use these steps.
Group Dates by Custom Date Range (Summary of Sales Data)
In the below pivot table, you have dates ranging from 01-Oct-2014 to 30-Jun-2015.

And you want to create a group of dates by month, but only for the 6 months of 2015, with all the months of 2014 in one group.
You can also create a group of dates by using a custom range and following these simple steps.
- Select a cell from the date field (dates column). Right-click on it and select “Group”.
- Untick the Auto boxes for “Starting at” and “Ending at”, and enter your custom dates.
- Select Months in the By list.
- In the end, click OK.
In the above pivot, you now have the dates grouped by months for the date range you entered, and the rest of the dates that are not in the date range are in a single category (it starts with “<”, like <01-01-2015).
With a custom date range, you must note that the dates before the start date go into one “<” group and the dates after the end date into one “>” group, whichever grouping (months, days or years) you pick.
Related: Excel Slicer
Group Dates in a Pivot Table with Two Fields (Without Changing Source Data)
Certain situations may require you to leverage more than a single time-span when grouping dates in a pivot table.
One such scenario is when you have a pivot table, and your goal is to organize or group the dates according to quarters and months.

- First, select a cell with a date in the date field (date column), right-click on it, and click Group.
- After that, select Quarters and Months in the By list (add Years too if your data covers more than one year).
- In the end, click OK.

Once you create more than one group for dates in the pivot table, you will also get an expanding and collapsing option to show or hide the months under each quarter.
Ungroup Dates in a Pivot Table in Excel
If you want to get back your dates or ungroup dates, you can do that with the “Ungroup” option.

- First, locate and click on the pivot table that contains the grouped data. This will activate the pivot table and prepare it for the ungrouping process.
- After you’ve selected a cell within the grouped data (date field), right-click on it. This will open a pop-up menu with several options, and the ‘Ungroup’ option is right below ‘Group’.
- Click ‘Ungroup’. This will instruct Excel to break up the grouped data within your pivot table. You can also use PivotTable Analyze ➜ Ungroup, or press Alt + Shift + Left Arrow.
Once you click ‘Ungroup,’ the data in the pivot table will be ungrouped instantaneously, and the previously grouped data will now be displayed as separate, individual entities.
Benefits of Grouping Dates in a Pivot Table
Grouping dates in a pivot table can come with a wide range of benefits, which include:
Enhanced Data Summarization: One of the primary benefits of grouping dates in a pivot table is that it allows for improved data summarization. By grouping dates, you can create a more comprehensive and meaningful data summary, making it easier to identify trends and patterns over specific periods.
Improved Data Analysis: Grouping dates in a pivot table can significantly improve your data analysis ability. You can perform a more detailed analysis by breaking complex data into more straightforward, understandable ones.
Flexibility: Another benefit of date grouping in pivot tables is its flexibility. Depending on the level of detail required, you can group data by different time intervals such as year, quarter, month, or day.
Time-Saving: Grouping dates in a pivot table can save you considerable time. It eliminates the need to add helper columns or to sort and filter the data. Instead, the pivot table automatically groups the data based on the specified time interval, which can significantly speed up the data analysis process.
Ease of Use: Despite their power and versatility, pivot tables are relatively simple to use and understand. The date grouping function is no different. Users without experience or familiarity with pivot tables can effectively group dates and analyze their data.
Frequently Asked Questions
Why do I get “Cannot group that selection”?
Your date column has blank cells or dates stored as text. Fix the source data (convert text to real dates and fill or remove the blanks), refresh the pivot table and try to group again.
Why is Group greyed out in my pivot table?
Make sure you have selected a cell in the date field, not a value cell. If the pivot table is built on the Data Model, date grouping works differently: add Year and Month columns to the source data or use the automatic date table instead.
Does grouping in one pivot table change other pivot tables?
It can. Pivot tables created from the same source data share a pivot cache, so grouping dates in one of them changes the grouping in the others too.
Hi Puneet – I’m finding your page extremely helpful, thanks very much.
Do you have detail on how to do the above date grouping using VBA instead? I’ve used your Pivot Table in VBA page to create mine with Date as a Row, but want to group by month in the code.
Thanks.
This is great, but I have a challenge. I have dates spanning over more than 12 months and when grouping by month, it combines the details for a month the 2 years into 1 row. How is this solved?
Is this for Excel 2016? When I right click the column I need to ungroup it doesn’t have group as an option. It’s messing up my pivot tables, I select to have the date and it breaks it up by quarters. I can’t see what I need to see and I need to send out this report like yesterday. 😬
Great way to show trick and tips appreciate it very much!!!!!!!!!!!!!!!1
Hi Puneet
I loved your guidance on the Grouping Option in Pivot Tables
Very clear explanations
Neil
Thanks a lots. greate
Thanks for sharing.
My favorite part of pivot table is “show report by filter pages”.
Thanks for sharing. 🙂
Stunning Trick, Many thanks
Welcome.
nice info…..
I’m so glad you liked it. 🙂
Nice information
Thanks for your words. 🙂
Really useful information presented in a crisp way. Thank you.
Thanks Sandip for your words.