Comparing years in Excel Pivot chart

A common requirement in the business world is to compare financial information year to year to see what the trend is. Pivot tables and charts are commonly used to slice and dice the information. However, it is not always easy when comparing years in excel pivot charts.

Years shown sequentially in most Pivot Charts

Below is what you may get when you run your report. You can see all the months in a straight line.

comparing years in excel pivot chart

However, you want to see the years on top of each other, something like the below to allow for comparing years in the Excel Pivot Chart (easier to spot any trends or seasonality).

comparing years in excel pivot charts

Make the Pivot Chart years appear on top of each other

To achieve this you need to make use of the Grouping feature of Pivot Tables.

Right click on one of the dates and choose grouping as shown below.

comparing years in excel pivot charts

Choose both the month and the year option and click OK as below

comparing years in excel pivot charts

You will notice that your Pivot Table now shows the months and years separately and in the fields you now have a Years and Months item.

When you now move the Years into the columns section you can compare the years against each other

Online Pivot Tables Course

Comparing 3 cells in Excel

Change the name of items directly in the Pivot Table

Turn off automatic date and time grouping in Excel Pivot Tables