How to Group Dates in Pivot Table (7 Ways)

In Microsoft Excel, the pivot table is one of those essential tools that help to analyze data efficiently. You can find various aspects of your dataset with the assistance of the pivot table. One of the beneficial features of the pivot table is group dates. In this tutorial, you will learn to group dates in a pivot table with suitable examples and proper illustrations. So, let’s dive into it.


Download Practice  Workbook


Turn Off Automatically Grouping of Date/Time in Excel Pivot Table

Now, before we start, you should know that in Microsoft Excel 2016 and later versions, the pivot table automatically group dates. You can turn off this setting in Microsoft Excel and later versions.

Note: Turning off this setting will also affect all the workbooks later. It is an Application-level setting in Excel.

Here, we are performing this on Excel 365.

📌 Steps

  • First, go to the File
  • After that, click on Options.
  • Then from the Excel Options dialog box, select the Data category from the left.

Turn Off Automatically Grouping of Date/Time in Excel Pivot Table

  • After that, check & mark on the following box:

Turn Off Automatically Grouping of Date/Time in Excel Pivot Table

  • Finally, click on OK to apply this new setting.

Now, this setting will automatically disable the grouping dates whenever you add dates in the pivot table field. If you don’t want this for other workbooks, change this setting again.


7 Ways to Group Dates in Pivot Table

In the following sections, we will provide you with six efficient ways to group dates in a pivot table. Now, we recommend you learn and apply all these methods to your worksheets. I hope it will definitely enrich your Excel knowledge in the future.

To demonstrate this tutorial, we are going to use the following dataset:

Group Dates by Years in Pivot Table

Here, we some product’s order dates, quantities, and their total prices. Now, we will make a pivot table of that and group the dates in every upcoming section.


1. Group Dates by Years in Pivot Table

Now, in our dataset, our order dates are between 2021 and 2022. Here, we want to group the total price based on years. That means we will find the sum of the total price of 2021 and 2022 separately.

📌 Steps

  • First, select any date from the pivot table.

  • After that, go to the PivotTable Analyze The, from the Group field, select Group Selection.

Group Dates by Years in Pivot Table

  • After that, you will find the following box:

Group Dates by Years in Pivot Table

  • Here, you can see the starting and ending dates of a group. You can change this according to your needs.
  • Now, select Years and click on OK.

Group Dates by Years in Pivot Table

As you can see, we are successful in group dates according to the years in the pivot table.

Read More: How to Group by Year in Excel Pivot Table (3 Easy Methods)


2. Group Dates by Months

Similarly, you can group dates based on Months in a pivot table. You already saw, there is a combination of months in our dataset. Now, we will group the sum of the total price according to that.

📌 Steps

  • First, select any date from the pivot table.

  • After that, go to the PivotTable Analyze Then, from the Group field, select Group Selection.

Group Dates by Years in Pivot Table

  • After that, you will find the following box:

Group Dates by Months

  • Now, select Months and click on OK.

Group Dates by Months

As you can see from the screenshot, we successfully grouped the dates based on months in the pivot table.

Read More: Pivot Table Custom Grouping: With 3 Criteria


Similar Readings


3. Group Dates by Months and Years Together

Now, another useful feature of these group dates is you can group them by months and years altogether. You can find the sum of total sales of each month in a particular year.

📌 Steps

  • First, select any date from the pivot table.

  • After that, go to the PivotTable Analyze Then, from the Group field, select Group Selection.

Group Dates by Years in Pivot Table

  • After that, you will find the following box:

Group Dates by Months and Years Together

  • Now, select Months and Years. After that, click on OK.

Group Dates by Months and Years Together

Finally, you can see the output from the above screenshot. You can group dates based on Months and Years in a pivot table easily.

Read More: How to Use Excel Pivot Table to Group Dates by Month and Year


4. Group Dates by Weeks in Pivot Table

Another important feature of the pivot table is group dates based on weeks. When you want to analyze your data based on a weekly basis, this method comes in handy. This method is quite difficult than others.

📌 Steps

  • First, select any date from the pivot table.

  • After that, go to the PivotTable Analyze The, from the Group field, select Group Selection.

Group Dates by Years in Pivot Table

  • After that, you will find the following box:

Group Dates by Weeks in Pivot Table

Here comes the tricky part. We don’t have any weeks here. All we are doing is splitting them into seven days. Remember the starting dates, if you want to start with any particular day of the week, change there.

  • After that, click on OK.

Group Dates by Weeks in Pivot Table

As you can see, we are successful in group dates on a weekly basis in the pivot table.

Read More: [Fix] Cannot Group Dates in Pivot Table: 4 Possible Solutions


Similar Readings


5. Group Dates by Quarters in Pivot Table

You can also analyze your data quarterly in the pivot table. You have to group them into quarters.

📌 Steps

  • First, select any date from the pivot table.

  • After that, go to the PivotTable Analyze The, from the Group field, select Group Selection.

Group Dates by Years in Pivot Table

  • After that, you will find the following box:

Group Dates by Quarters in Pivot Table

  • Now, select the Quarters and click on OK.

  • Now, the issue with this is, here all the quarters also include the quarters both 2021 and 2022. You can differentiate them from here. So, to group them in separate years do the following:

Group Dates by Quarters in Pivot Table

  • Now, select both Quarters and Years. After that, click on OK.

Group Dates by Quarters in Pivot Table

As you can see, we grouped the dates based on Quarters in the pivot table.

Read More: Excel Table Formatting: Problems and Fixes You Need to Know


6. Group Dates by Hours/Seconds/Minutes

You can group dates into Hours if your dataset looks like this:

Here, we just changed our dataset into a single-day transaction. Basically, we split our whole day into hours.

If you want to group your dates on an hourly aspect, follow these simple steps.

📌 Steps

  • First, select any date from the pivot table.

  • After that, go to the PivotTable Analyze Then, from the Group field, select Group Selection.

Group Dates by Years in Pivot Table

  • After that, you will find the following box:

Group Dates by Hours/Seconds/Minutes

  • Then, select Hours, Minutes, and Seconds and click on OK.

Group Dates by Hours/Seconds/Minutes

Read More: Excel Pivot Table Auto Grouping by Date, Time, Month, and Range!


7. Group Dates by Adding Extra Items in Pivot Table

When you add data before starting date or after the ending date, the pivot table represents them with “<” or “>” signs.

After grouping dates, you can find this in the filter section.

By this, you can find the sum of the total price after you have added the newest data.

Read More: Excel VBA Code for Each Row in a Table (Add, Overwrite, Delete, etc.)


Ungroup Dates in Pivot Table

If you don’t want to group your dates more, you can easily ungroup them by following these simple steps:

📌 Steps

  • First, select any element of your grouped date.

  • Then, right-click on your mouse.

Ungroup Dates in Pivot Table

  • Then, click on Ungroup.

After that, you will see, there are no dates in a particular group of that pivot table.


💬 Things to Remember

You can easily group dates in the pivot table by right-clicking and selecting the Group command.

When you group dates based on Days, some calculated fields won’t work.

If your dataset doesn’t contain the hours, minutes, and seconds, it won’t produce the output like this article.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to group dates pivot table in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo