Comparison of Yearly and Monthly Data using Pivot Chart

If you have daily data in Excel for two years, you can use a pivot table and pivot chart to quickly compare that data, month by month, year over year. This video shows how to compare monthly and annual data in Excel pivot charts. I have shown how to create a pivot table and make a pivot chart that lets you compare two years of data.

To create a pivot table and pivot chart, you have to:

  • Select any cell in the work orders table 
  • On the Excel Ribbon, click the Insert tab
  • From Insert tab, click Pivot Table
  • You can choose whether you create a pivot table in the existing worksheet or in a new worksheet.
  • From pivot table analyze, you can click on pivot chart and create an appropriate pivot chart. I have selected a line chart.

Group Dates by Months and Year

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

  • On the pivot table, 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.

Change to Year over Year

  • To show separate lines for each year, I dragged the years field from the pivot table field list from row label to column label. 
  • After moving the years field, the pivot chart will show two separate lines – one for each year. 


After creating the comparative line chart, I have done some quick formatting.

  • I hide all the field buttons from the chart by right clicking on the field button and then clicked “hide all field buttons from the chart”.
  • I have also right clicked on the legend and clicked the format legend. 
  • For the legend option, I chose top. 

Leave a Comment

Your email address will not be published.

Shopping Cart