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.
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.
To see the steps for comparing years in an Excel pivot chart, watch this short video. There are written steps below the video.
If you create a chart from the pivot table, when all the dates are listed, it’s impossible to see the details.
You can see a few dates that have higher numbers than the others, but you can’t see monthly totals, or 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.
Follow these steps to group the dates and create a line for each year:
The pivot chart layout will change, and each year will each be represented by a separate line.
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.
If you do lots of work with charts in Excel, save time with Jon Peltier's Excel Chart Utility.
Last updated: September 10, 2019 4:22 PM