Contextures

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

numberformat01

Video: Pivot Chart Compare Years

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.

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
  • 03:15 Get the Sample File

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, we want compare the number of service call work orders completed each month, year over year.

2 years of data from service calls

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

pivot chart command

  • 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

Create PivotChart window

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, right-click on WO
  • Click on Add to Values

PivotChart Fields list

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

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

pivot chart with years

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

Expand/Collapse command

Individual Dates Showing

f 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

group by Months and Years

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

Change Chart Type command

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

choose line chart type

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.

drag Years field into the Legend

Pivot Table

drag Years field into 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 can't change a pivot chart, without affecting the pivot table that it's based on.

pivot table and pivot chart year over year

Pivot Chart Formatting

After you have the pivot chart set up to show a separate line for each year, you can clean up the formatting, if you'd like to. 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

  • 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

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

pivot chart with formatting changes

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
  • Click the Data category, and at the end of the Data options section, add a check mark to "Disable automatic grouping of Date/Time columns in PivotTables"
  • Click OK to apply the new settings.

grouping settings in Excel options

Get the Sample File

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.

Video Transcript

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.

Related Tutorials

Pivot Table Grouping

Pivot Chart Number Formatting

Pivot Chart Field List Macro

Pivot Table Introduction

Pivot Chart Source Data

 

Get weekly Excel tips from Debra

 


Last updated: July 11, 2021 7:41 PM