How to create a YOY comparison chart using a PivotChart in Excel

March 30, 2022 0 Comments

Need to know YOY results for your organization? Susan Harkins will show you how to create a PivotChart in Microsoft Excel.

This Excel pivotchart shows a YOY comparison of invoices for three years.
You can create an Excel pivotchart to see results like this year after year.

Many organizations compare revenue and other assets from one year to the next to assess financial performance and refer to it as year after year analysis. By measuring revenue or any other asset, you can see if the organization is growing as intended. In this article, I will show you how to create a YOY chart using PivotTable and PivotChart features of Microsoft Excel.

See: Software Installation Policy (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use an earlier version. I recommend that you stop upgrading to Windows 11 until all issues are resolved. For your convenience, you can download the Exhibit .xlsx file. This feature is not supported by the older .xls format Excel for the Web supports both PivotTables and PivotCharts in an existing .xlsx file. You can create a PivotTable in Excel for the web, but you cannot group columns. Or you can’t insert a PivotChart.

What is a year-over-year chart?

Most companies rely on YOY charting to see if they are growing at a rate that meets their goals. You do not have to compare income. For example, a call center can compare the increase or decrease of service calls in the last few years. In addition, a company looking for investors or trying to sell a company must present a favorable YOY analysis. This is an effective way to evaluate performance.

Shown on the YOY chart Figure A Displays a line for each year in the data set. If you want to compare 2021 and 2022 from the first quarter of 2022, you want to see three lines একটি one for each year, as shown in Figure A. What data points represent depends on your needs. In this case, the chart compares the number of shipments per month.

Figure A

This Excel pivotchart shows a YOY comparison of invoices for three years.
This Excel pivotchart shows a YOY comparison of invoices for three years.

These comparisons can be fairly simple or very complex depending on how the business stores its data. If you can, I recommend starting with a simple database type list, and if you are using Excel to analyze that data. For example, in sheet Figure B Saves customer invoice information. Each record identifies the customer, shipping date, invoice month and invoice total. (You can see only a few records for 2022, but the table contains dozens of records for 2020, 2021 and 2022.)

Figure B

This data set structure supports our needs.
This data set structure supports our needs.

At this point, you know what a typical YOY PivotChart looks like and its purpose, and you see the source data. Let’s go ahead and create a PivotTable to support PivotChart (Figure A)

How to create a PivotTable in Excel

Thanks to the data structure, creating PivotTable will be easy. But before we do anything, let’s review what we want. For this example, we want to calculate the number of shipments by year and month. This means we have to group the records by month and year. If it sounds complicated, don’t worry, it’s not.

Let’s create a pivot table and continue:

  1. Click anywhere inside the table data set.
  2. Click the Insert tab and select PivotTable in the table group.
  3. Excel does a good job of determining your needs, as you can see Figure C. Take a moment to review the settings, and then click Insert

Figure C

Insert the PivotTable frame into a new worksheet.
Insert the PivotTable frame into a new worksheet.
  1. On a new sheet, you’ll see a blank PivotTable frame and a list of fields.
  2. Use Figure D As a guide, drag the fields from the list down to the appropriate list control. (Pivot table does not require your customer ID.)

Figure D

Define PivotTable by dragging fields to the appropriate location list.
Define PivotTable by dragging fields to the appropriate location list.

Where do you think the Years field came from? Excel automatically adds a date field to the source data.

If your PivotTable is not the same, right-click any cell of the month (first field on the left) and select Group. Make sure the month and year are selected according to the list, as shown Figure e. It is possible that you may have to opt for quarters or days.

Figure e

Group PivotTable by month and year.
Group PivotTable by month and year.

PivotTable data calculates invoices by month and year, when creating PivotChart (Figure A)

How to create PivotChart in Excel

At the moment, you have a PivotTable that counts the number of shipments per month and year. From here, it is easy to create pivot charts year after year:

  1. Click anywhere inside the PivotTable you have created.
  2. Click the Insert tab and select PivotChart from the chart group.
  3. In the resulting dialog, select the line to the left of the list and click OK.
  4. Figure F Shows two pivot objects side by side.

Figure F

PivotChart works mostly, but PivotChart is what your peers will want to see.
PivotChart works mostly, but PivotChart is what your peers will want to see.

It’s easy! Since PivotTable grouping works, PivotChart requires almost no input from you. PivotTable tells a story that neither data nor PivotTable can tell. Looks like the first quarter of 2022 is off the chart, as they say. Of course, the number of invoices does not reflect the actual income. To do this, you need to change the PivotTable.

How to add invoices to Excel

YOY PivotChart may be helpful to someone in your team, but most everyone will be interested in revenue. To compare three-year earnings we need to add a quantity field to the pivot table. To do this, right-click anywhere in the PivotTable and select Refresh from the resulting submenu. Doing so will add amount to the field list from the data source.

Drag the quality control list from the field list and then delete the invoice field, as shown Figure G..

Figure G.

Add a quantity field from the original data source.
Add a quantity field from the original data source.

To delete it, click the dropdown and select Remove Field from the resulting submenu. Figure H Shows updated PivotChart. 2022 is starting to be a great year! In particular, March 2022 was an amazing month.

Figure H

Our new pivotchart.
Our new pivotchart.

This type of chart provides a lot of information, and thanks to Excel’s two pivot objects, you don’t have to go through hoops to create it.

Leave a Reply

Your email address will not be published.