Group Dates in a Pivot Table by Months, Weeks, Quarter, & Years

By grouping dates in a pivot table, you can create instant reports.

Here’s the point: Let’s say you want to group all the dates as months instead of adding a different column in your data; it’s better to group 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:

1. 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.
2. After selecting the dates, right-click on any of the highlighted dates. This will bring up a context menu with several options.
3. 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.
4. 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 visualize your data in a more manageable and understandable format, making informed decisions based on it.

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 below steps to this.

1. To start, you can select any dates from the data field. And then right click on it and further click on the group option.
2. Select the “days” option from the group by option in the dialog box. Enter 7 in several days.
   
3. 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 week created by the pivot table is not based on Mon-Sun.

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.

1. Select any of the cells from the date field (dates columns).
2. Right click on it and select group.
3. Select hour from the group by option.
   
4. Click OK.
   

You can also use the above steps to group dates in the pivot table by minutes and seconds.

In the same way, grouping data by minutes and seconds in a pivot table ensures your data is in the correct time format.

But make sure to convert your data to a time format before these steps can be used.

Group Dates by Custom Date Range (Summary of Sales Data)

In the below pivot table, you have dates ranging from 01-Oct-2014 to 31-Jun-2015.

And you want to create a group of dates by month, but only for 6 months of 2015 and 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.

1. Select a cell from the date field (dates column). Right-click on the and select “group”.
2. Untick starting and ending date in auto option. And, enter your custom dates.
3. Select month from by group option.
   
4. In the end, click OK.
   

In the above pivot, you now have dates in a group by months for the date range you mentioned, and the rest of the dates that are not in the date range are in a single category.

With this method of automatic grouping of dates, you must note that you can’t use any other method to group dates, such as months, days, or years.

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.

1. First, you can select a cell with a date in the date field (date column) and right-click on it to open the right-click menu.
2. After that, select quarters and months.
3. 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

Ungroup Dates in a Pivot Tables in Excel

If you want to get back your dates or ungroup dates you can do that with the “ungroup‘ option.

1. 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.
2. 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. In the dialog box that appears, navigate to the ‘Group’ option. This is typically near the middle or bottom of the menu.
3. After clicking on ‘Group,’ another menu will appear. This sub-menu contains additional grouping options. From this sub-menu, select ‘Ungroup’. This option will instruct Excel to break up the grouped data within your pivot table.

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 sort or filter 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.

Sample File

download