Compare Annual Data in Excel Pivot Chart

If you have a couple of years of daily data in Excel, you can use a pivot chart to quickly compare that data, month by month, year over year. This short video shows how to compare annual data in Excel pivot chart.

Video: Compare Years in Pivot Chart

This video shows how to create a pivot table, and make a pivot chart that lets you compare two years of data.

Work Orders Data

In this example, there is a named Excel table with 2 years of data from service call work orders.

A pivot table and pivot chart were created from that data, with 2 fields added to the layout:

Count of Work Orders – Values area
Work Date – Rows area (pivot table) / Axis fields (pivot chart)

Two Fixes for Pivot Chart

There are 2 key things to fix in the pivot chart, if you want to compare the work order counts per month, year over year:

Group dates by year and month
Move Year to Columns area (pivot table) / Legend area (pivot chart)

There are detailed instructions for these fixes on the Pivot Table Compare Years page of my Contextures site, and short notes below.

Group Dates by Year and Month

After you add the Work Date field, depending on your Excel version, and your option settings, you might see

all the dates listed individually (as shown in the video)
OR just the years listed (in the screen shot below)

Show the Months

If your pivot chart is showing the years, the next step is to show the months. (If all dates are showing, go to the next section)

If a Quarters field was automatically added, remove that from the layout
In the pivot table (not the pivot chart), right-click on one of the year numbers
Point to the Expand/Collapse command
Click on the Expand Entire Field command

Group Dates by Month and Year

If your pivot chart is showing individual dates, the next step is to fix the date grouping.

In the pivot table (not the pivot chart), right-click on one of the dates
Click the Group command
In the Grouping window, the Starting at and Ending at boxes will show the first and last dates from the Work Date field
In the “By” list, click on Months and Years, then click OK

Line Chart Year Over Year

For the pivot chart, choose the Line chart type. That will create a chart with a single line for the two-year time period.

Here’s how to create a separate line for each year:

Click on the pivot chart to select it
In the PivotChart Fields List, drag the Years field into the Legend (Series) area.

Two Separate Lines in Pivot Chart

After you move the Years field, the pivot chart will show two separate lines – one for each year.

The pivot table layout also changes, with the years as column headings, across the top.

NOTE: You can’t change a pivot chart, without affecting the pivot table that it’s based on.

Get the Pivot Chart Sample File

To get the workbook with the Work Order data, go to the Pivot Chart Compare Years page on my Contextures website.

The zipped file is in xlsx format, and does not contain any macros.

___________________