How to Group by Year in Excel Pivot Table?

Get FREE Advanced Excel Exercises with Solutions!

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.

excel pivot table group by year


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 pivot table group by year

  • Excel will create a pivot table.

  • Now, choose Date and drag it to Rows.

excel pivot table group by year

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

excel pivot table group by year

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.

excel pivot table group by year

  • A new box named Grouping will pop up. Select Years.

  • Excel will group the dates by year.

excel pivot table group 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.

excel pivot table group by year

  • A new box named Grouping will pop up. Select Years.

  • Excel will group the dates by year.

excel pivot table group 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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo