Easy steps show how to compare years in Excel pivot chart. Short video and written steps show how to group dates by year and month, and how to set up the pivot chart for year over year comparison.
A pivot table is a quick and effective way to summarize data, and you can also create a pivot chart, to show a visual summary. If you’re summarizing the data by date, you’ll usually need to group the date field, to get a chart that’s easy to read.
To see the steps for comparing years in an Excel pivot chart, watch this short video. There are written steps below the video. The full transcript is at the end of the page.
The video timeline is below the video.
In this example, there is a named Excel table with 2 years of data from service calls at a manufacturing company.
From this data, we want compare the number of service call work orders completed each month, year over year.
A pivot table will summarize the work order data, and a pivot chart will show a visual summary.
Follow these steps to create the pivot table and pivot chart, both at the same time.
After you click OK, an empty pivot table and pivot chart are added to your workbook.
Follow these steps to add a count of the work orders (WO field)
The screen shot below shows the pivot table and pivot chart, after you add the WO field.
Next, follow these steps to add the Work Date field, so the work order count can be compared year over year.
Depending on your Excel version, and your option settings, the pivot table and pivot chart might show:
This screen shot is from Excel for Office 365, with its default option settings, where dates are automatically grouped by year.
We want to compare work orders for each month, year over year.
NOTE: If your pivot table and pivot chart DO NOT have a Years field, go to the next section, to add years and months.
If your pivot table and pivot chart already have a Years field, follow these steps to add the months:
f your pivot chart is showing individual dates, follow these steps to group by year and month
To compare the monthly totals, year over year, follow these steps to change the chart type from column chart to line chart:
The pivot chart now shows a line, but we want a separate line for each year, not a single line for the two-year time period. You can do the next step in the Pivot Chart, or in the Pivot Table.
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 cannot change a pivot chart, without affecting the pivot table that it's based on.
After you have the pivot chart set up to show a separate line for each year, you can clean up the formatting, to make the chart easier to read. Here are a few suggestions:
Value Button: In the pivot table, change "Count of WO" to "Work Orders", in the top left cell. That will change the label at the top left of the pivot chart.
Legend: In the pivot chart, right-click the Legend, and click Format Legend
Axis Button: Right-click on the WorkDate button, and click Hide Axis Field buttons on Chart
Here's the pivot chart, after making those changes.
In Excel 2016 and later versions, when you create a Pivot Table, Excel automatically groups the dates into years and months.
If you'd prefer to see individual dates, follow these steps to change your Excel options.
NOTE: This is an application-level setting, and will affect all your Excel workbooks.
Click here to download a copy of the Pivot Chart Compare Years sample file. The zipped file is in xlsx format, and does not contain any macros.
Compare Years in Excel Pivot Chart
In this workbook, we have service data that shows the date of each work order. and we'd like to create a summary, to show the number of work orders each month, comparing 2013 and 2014.
So we'll see how we can set up a pivot table to summarize the data and then a pivot chart that shows the years.
Here's a sheet with all the raw data. We've got it set up as an Excel table. Each work order has a number and we can see the request date and the date the work was done.
First step will be to create a pivot table from this data, to summarize things.
Click anywhere in this table, and on the Insert tab, I'll click Pivot Table.
This list is a formatted Excel table, a named table, so we can see that it's picked up the name automatically. And so if we add new records, this will expand to include those.
And I'd like this chart on an existing worksheet. I'll click in here, click this box. I would like it on ServiceChart1, cell A3. Close that, click.OK.
So here's the empty pivot table, and I'm going to put in the work date, and then I'll put the work order into the values and it will show up as a count.
So now we have everything summarized by date and I'm going to create a chart to see how it looks now.
So go back to the Insert tab. I'll click Pivot Chart, and it's suggesting a column chart, but I would like a line, and I'd like this type of line and I'll click OK.
And it's put in a very bumpy line chart for me. So it's charting each day, and I would like it by month and year.
So I'm going to have to group the dates. Right now, all the dates are listed individually, so I'll right click on a date and Group.
I'd like them grouped by years and months, so I'll select both of those, click OK, and now the chart looks much better.
It's showing a single line, and we can see the months across the bottom, 2013 followed by 2014. We'd like to compare year to year though, so I need to create a single line for each year.
And to do that, I'm going over to the pivot table field list and I can see Years and Work Date, here in the rows. And to get two separate lines, I'm going to drag Years up into columns, and when I let go, it creates a line for each year.
So that's the layout we want. You can do a couple of other things, to make this a little easier to read, and more appealing to look at.
So here where it says Count of Work Order, if I go to the pivot table and type Work Orders, and I press Enter, that shows up in the pivot chart as well.
And if I don't want this button at the bottom, I can right click on it and hide the Axis field buttons.
Last updated: October 23, 2021 11:49 AM