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. 

 Formatting

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.