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.