A.N.M. Mohaimen Shanto
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.
- 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.
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:
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.
- 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.
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.
- 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.
Read More: Pivot Table Custom Grouping: With 3 Criteria
Similar Readings
- How to Group Dates in Excel Chart (3 Easy Ways)
- Group Dates in Excel Slicer (4 Ways)
- How to Group Dates by Filter in Excel (3 Easy Methods)
- Pivot Table is Not Picking up Data in Excel (5 Reasons)
- [Fixed!] Pivot Table Field Name Already Exists (2 Quick Methods)
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.
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.
- 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.
Read More: [Fix] Cannot Group Dates in Pivot Table: 4 Possible Solutions
Similar Readings
- How to Group Columns in Excel Pivot Table (2 Methods)
- How to Get a Count in Excel Pivot Table Calculated Field
- Make Group by Different Intervals in Excel Pivot Table (2 Methods)
- How to Refresh All Pivot Tables in Excel (3 Ways)
- How to Compare Two Tables for Differences with Excel VBA (3 Methods)
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.
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.
- 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.
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.
- 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
- Types of Tables in Excel: A Complete Overview
- Does TABLE Function Exist in Excel?
- Convert Table to List in Excel (3 Quick Ways)
- How to Lookup a Table and Return Values in Excel (3 Simple Ways)
- Applications of Absolute Structured References in Excel Table Formulas
- How to Lookup a Table and Return Values in Excel (3 Simple Ways)
