Excel is the most widely used tool when it comes to dealing with huge datasets. We often need to group dates by year in Excel. We can easily do this using the Pivot Table. In this article, I will explain how to show the Excel Pivot Table grouped by year.
How to Group by Year in Excel Pivot Table: 3 Easy Methods
This is the dataset that I am going to use. I have some Sales Persons along with the Dates and Sales Amounts. I will group these dates by year using 3 different techniques.
1. Use Pivot Table Fields Feature to Group by Year
The simplest way to group dates in Pivot Table is to use PivotTable Fields. I will show the steps one by one.
Steps:
- Select the entire dataset. Then go to the Insert tab >> PivotTable >> select From Table/Range
- A window will appear. Put your range and then select New Worksheet to get the table in a new worksheet. Click OK.
- Excel will create a pivot table.
- Now, choose Date and drag it to Rows.
- As soon as you do so, you will see that Excel automatically groups the date.
- Now simply put the Sales Person to the Columns field and Sales Amount to the Values Your pivot table will like this.
After that, you can format the table as you wish.
Read More: How to Group by Week in Excel Pivot Table
2. Apply Pivot Table Analyze Tab Option to Group Dates by Year
There is another way of grouping dates by year in the Excel Pivot Table. By applying the PivotTable Analyze tab, we can do so. Let’s see how this method works.
Steps:
- Create a pivot table following method-1. The table will look like this.
- Now select any date. Then go to the PivotTable Analyze tab >> select Group >> select Group Field.
- A new box named Grouping will pop up. Select Years.
- Excel will group the dates by year.
Read More: How to Group by Month in Excel Pivot Table
3. Utilize Context Menu Command to Group by Year in Excel Pivot Table
We can also use the context menu to group dates by year.
Steps:
- Create a pivot table following method-1.
- The table will look like this.
- Now select any date and right-click your mouse to bring the context menu. After that select Group.
- A new box named Grouping will pop up. Select Years.
- Excel will group the dates by year.
Things to Remember
- Method 1 works for Excel version 2016 or above. If you use other versions, then you can perform the Excel Pivot Table group by year using method 2Â and method 3.
Download Practice Workbook
Download this workbook and practice while going through the article.
Conclusion
In this article, I have demonstrated 3 effective methods to show Excel Pivot Table group by year. I hope it helps everyone. If you have any kind of suggestions, ideas, or feedback, please feel free to comment down below.
Related Articles
- How to Group by Week and Month in Excel Pivot Table
- How to Group by Month and Year in Excel Pivot Table
- [Fix] Cannot Group Dates in Pivot Table
- [Fixed] Excel Pivot Table Not Grouping Dates by Month
<< Go Back to Group Dates in Pivot Table | Group Pivot Table | Pivot Table in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!