Akib Bin Rashid
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 Excel Pivot Table group by year.
Download Practice Workbook
Download this workbook and practice while going through the article.
3 Easy Methods to Group by Year in Excel Pivot Table
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 >> selet 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 the 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 Use Excel Pivot Table to Group Dates by Month and Year
Similar Readings
- How to Group Dates by Filter in Excel (3 Easy Methods)
- How to Group Pivot Table by Month in Excel (2 Methods)
- How to Group by Week and Month in Excel Pivot Table (with Easy Steps)
2. Apply Pivot Table Analyze Tab Option to Group Dates by Year
There is another way of grouping dates by year in 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: [Fix] Cannot Group Dates in Pivot Table: 4 Possible Solutions
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 Excel Pivot Table group by year using method-2 and method-3.
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.
