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.

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 tab.
• After that, click on Options.
• Then from the Excel Options dialog box, select the Data category from the left.

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

• 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.

How to Group Dates in Pivot Table: 7 Possible Ways

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:

Here, we have some product 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.

• After that, you will find the following box:

• 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.

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

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.

• After that, you will find the following box:

• Now, select Months and click on OK.

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

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.

• After that, you will find the following box:

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

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

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.

• After that, you will find the following box:

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.

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

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.

• After that, you will find the following box:

• 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:

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

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

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.

• After that, you will find the following box:

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

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.

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.

• 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.

Download Practice  Workbook

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.

Keep learning new methods and keep growing!

Group Dates in Pivot Table : Knowledge Hub

<< Go Back to Group Pivot Table | Pivot Table in Excel | Learn Excel