How to Group by Year in Excel Pivot Table (3 Easy Methods)

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.

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

  • Excel will create a pivot table.

  • Now, choose Date and drag it to the 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 Use Excel Pivot Table to Group Dates by Month and Year


Similar Readings


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.

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: [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.

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


Related Articles

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo