Home > Pivot > Charts > Pivot Chart Pivot Chart Compare Years 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.

Introduction

A pivot table is a quick and effective way to summarize datain Microsoft Excel, 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.

Video: Pivot Chart Compare Years

To see the steps for comparing years in Excel pivot charts, 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.

Video Timeline

• 00:00 Introduction
• 00:22 Work Orders Table
• 00:38 Create a Pivot Table
• 01:14 Add Fields to Pivot Table
• 01:19 Create a Pivot Chart
• 01:54 Group the Dates
• 02:24 Show Year Over Year
• 02:49 Final Touches to Chart

Source Data for Pivot Table

In this example, there is a named Excel table with 2 years of data from service calls at a manufacturing company.

From this data source, we want compare the number of service call work orders completed each month, year over year.

Create Pivot Table and Chart

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.

• Select any cell in the work orders table
• On the Excel Ribbon, click the Insert tab
• In the Charts group, click Pivot Chart

• Next, in the Create PivotChart window, the table name (WorkOrders) should automatically appear in the Table/Range box
• Select a location for the pivot table
• DO NOT check the box for "Add this data to the Data Model" - that will prevent grouping
• Click OK

Add Order Count

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)

• Select the pivot chart - if it's not already selected
• At the right, in the PivotChart Fields list, you can see each field name (column label) from the source data
• In that field list, right-click on WO - the Work Order number field
• In the pop-up menu, click on Add to Values

Order Count in Pivot Chart

The screen shot below shows the pivot table and pivot chart, after you add the WO field.

• Because the Work Order codes are text, the value is summarized with a count of the orders.
• The WO count is also added to the pivot table, automatically.
  • If you change the layout of the pivot chart, the same changes are applied to the pivot table layout, automatically
  • If you change the layout of the pivot table, the same changes are applied to the pivot chart layout, automatically

Add Work Date

Next, follow these steps to add the Work Date field, so the work order count can be compared year over year.

• In the PivotChart Fields list, add a check mark to the WorkDate field
• Excel automatically adds this date field to the Axis fields (Categories).

Pivot Chart Date Grouping

Depending on your Excel version, and your option settings, the pivot table and pivot chart might show:

• all the dates listed individually (as shown in the video)
• OR a new pivottable field named Years, showing years from the source data (in the screen shot below)
  • Years field is collapsed, so the individual work dates aren't listed
  • Each year number is a row label in the pivot table

This screen shot is from Excel for Office 365, with its default option settings, where dates are automatically grouped by year.

Show the Months

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.

Years Already Showing

If your pivot table and pivot chart already have a Years field, follow these steps to add the months:

• In the pivot table (not the pivot chart), right-click on one of the Years
• Point to the Expand/Collapse command
• Click on the Expand Entire Field command

Individual Dates Showing

If your pivot chart is showing individual dates, follow these steps to group by year and month

• 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 WorkDate field
• In the "By" list, click on Months and Years, then click OK

Change Chart Type

To compare the monthly totals, year over year, follow these steps to change the chart type from column chart to line chart:

• Right-click on the pivot chart
• Click the Change Chart Type command

• In the list of chart types, click on Line
• Choose the first line chart option – Line, and click OK

Change Chart to Year Over Year

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.

Pivot Chart

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

Pivot Table

• Click on any cell in the pivot table
• In the PivotTable Fields List, drag the Years field into the Columns area.

Pivot Chart Shows Year Over Year

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.

Pivot Chart Formatting Tips

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 Excel 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

• For the Legend position, choose Top, and uncheck the option to Show the legend without overlapping the chart
• Right-click on the Legend button, and click Hide Legend Field buttons on Chart
• Then, point to the Legend's border, and drag the Legend to a new position, if necessary, so it doesn't cover the lines

Axis Button: Right-click on the WorkDate button, and click Hide Axis Field buttons on Chart

Chart Title: Delete the generic chart title, or change the title to something meaningful, such as "Monthly Work Orders"

Here's the pivot chart, after making those changes.

Move the Pivot Chart

If you'd prefer to have the pivot chart on its own sheet, you can move it. Follow these steps to move the pivot chart to either a different worksheet, or to a special chart sheet:

• Right-click on the pivot chart border
• In the pop-up menu, click the Move Chart command
• In the Move Chart dialog box, click one of the radio buttons, to choose a location option:
  • New Sheet: Chart will be moved to its own Chart Sheet - change the default name to something meaningful
  • Object In: Select an existing worksheet name - chart will be moved to that sheet
• Click OK to complete the chart move

Pivot Table Date Grouping Settings

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.

• On the Ribbon, click the File tab, then click Options
• In the list at the left, click the Data category
• At the right, scroll down to the end of the Data options section
• Add a check mark to the setting, "Disable automatic grouping of Date/Time columns in PivotTables"
  • That option is highlighted in the screenshot below
• Click OK to apply the new settings.

Download the Sample File

• Compare Years: 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.

Note: If you want to try this technique with other types of sample data, such as food sales data, or hockey players, go to the Excel Sample Data page, and take a look at the sample files there.

Video Transcript

Compare Years in Excel Pivot Chart

• Go back to Video

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.

• Go back to Video

Related Tutorials

Pivot Chart Clustered Stacked Compare Years

Pivot Table Grouping

Pivot Chart Number Formatting

Pivot Chart Field List Macro

Pivot Table Introduction

Pivot Chart Source Data