Contextures

Pivot Chart Compare Years

When a pivot chart is created, the numbers on the chart's axis are formatted the same as the pivot table's numbers. See how to change the number format in both places, or in just the pivot table.

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

In this example, the source data contains records from the service department at a manufacturing company. The pivot table counts the number of work orders that were completed each day. We’ll build a chart that compares last year’s monthly counts to this year’s.

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.

Create a Pivot Chart

If you create a chart from the pivot table, when all the dates are listed, it’s impossible to see the details.

chart with all dates

You can see a few dates that have higher numbers than the others, but you can’t see monthly totals, or compare years.

Change Chart to Compare Years

To compare the monthly totals, year over year, you’ll need to group the date field in the pivot field, by Years and Months.

chart with dates grouped by year

Follow these steps to group the dates and create a line for each year:

  1. In the pivot table, right-click a cell in the date field, and then in the popup menu, click Group.
  2. In the Grouping dialog box, select Years and Months, and click OK.
  3. In the PivotTable Field List, move the Years field to the Column Labels area.

The pivot chart layout will change, and each year will each be represented by a separate line.

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

Excel Chart Utility

If you do lots of work with charts in Excel, save time with Jon Peltier's Excel Chart Utility.

Peltier Tech Charts for Excel 3.0

Related Tutorials

FAQs - Pivot Tables

Pivot Chart Number Formatting

Pivot Chart Field List Macro

Pivot Table Introduction

Pivot Chart Source Data

30 Excel Functions in 30 Days

 

 

 

Get weekly Excel tips from Debra

 

 

 

 

 

pivot power premium

 

 

 

pivot xtreme

 

pivot power premium

 


Last updated: September 10, 2019 4:22 PM