How to create a Cycle Plot (Panel Chart) in Excel

A Cycle Plot (panel chart) is a small comparison chart that consolidates charts using the same scale. Sometimes, we work with custom reports and need a space-saving solution. The cycle plot is a perfect chart for tracking and analyzing data in a small space.

Today’s guide will examine the Cycle Plot more closely. In the example, you will learn how to compare the sales performance of two companies over five years.

The bad news: Excel does not support the cycle plot. If you want to build this chart in seconds, use our chart add-in. A few clicks are necessary, and you can forget the manual work. Using our step-by-step tutorial, let’s see how to create a Cycle Plot in Excel.

How to create a Cycle Plot in Excel

#1. Prepare the Data

Let’s start with the initial data set because we want to show you every step. In the example, we will compare the data sets of the two corporations, ACME Corp. and Umbrella Corp, using four different products in five years. To create a cycle plot, you need to prepare and use the structure below:

If you have time, you can manually create the structure on the right side. However, we recommend using Pivot Tables to extract the data from the table.

#2. Add separators to split line charts

We need to insert a helper column to create a cycle plot in Excel. Insert a new column! Use “1” for the range F2:F7 and F13:F17. Apply “2” for ranges F8:F12 and F18:F22.

This step is important; you’ll see why we need a small extra data.

#3. Create a new Pivot Table

Select the range, locate the Insert Tab, and use Pivot Table.

After clicking PivotTable, a new window appears. You can create the table using a new Worksheet. In the example, the right decision is to place the Pivot table in the existing Worksheet.

Select the location and insert the Pivot table.

use an existing worksheet for the pivot table location

#4: Build a proper layout using fields

After clicking OK, a blank pivot table will appear. On the right side, you should have to use the PivotTable Fields task pane to build a usable output for our cycle plot.

Place the Product and Year in Rows.
Move “Divider” to “Columns area.”
Add the “Acme Corp.” and “Umbrella Corp.” to the “Values” field.

Result:

To remove subtotals and grand Totals and simplify the pivot table, follow the following steps: First, select any cell in the pivot table and locate the Design Tab.

Click the Subtotals icon and choose the “Do not show Subtotals.”
From the “Grand Totals” menu, pick the “Off for Rows and Columns.”
Use the Report Layout menu and select the “Show in Tabular Form.”

Our data set looks like in the picture below:

#5. Copy data and Create a Header

Select the Pivot Table data and copy all cells into a new location.

Add headers to the new table.

#6. Insert line charts

Select the data to create a simple line chart for the cycle plot. After that, go to the Insert tab. Finally, choose a line chart.

#7. Format data series

The next step is to simplify the chart. We want to use the same colors for the variables. To do that, select the series by right-clicking and choosing “Outline.” In the example, we use purple for the Umbrella Corp. and Orange for the Acme Corp.

You should not have to reopen this dialog box. We can apply different colors without using the task pane. Instead, use the drop-down list and select the given series. It’s easy to add colors for each corporation.

#8. Add a helper table for dividers

Creating dividers for the merged line charts is not an easy transformation. To create a cycle plot, you have to use vertical error bars. In the next chapter, we’ll show you how to create dividers using the right position.

To create dividers, we need to build a small helper table.

Leave the cell X4 blank, and fill the cells using zeros in column Y.
Enter the following formula in cell X5. =COUNTA(P4:P8)+0.5
Apply the =X4 + COUNTA(P9:13) formula in cell C6.
Copy the formula down

#9. Create a dummy series for the cycle plot

Let’s see the key steps!

Select and copy the helper table data
Select the chart area.
On the Home tab, click Paste and apply “Paste Special.”

insert a dummy series for the cycle plot

A new popup window will appear. Use the following settings:

Under the “Add cells as” group, click “New series.”
Under the “Values (Y) in” group, select “Columns.”
Enable the “Series Names in First Row” option
Enable the Categories (X Labels) in First Column” option
Finally, click “OK.”

#10. Change the inserted Chart Type

The inserted data series is shown in the picture below. Right-click the line and select the “Change Series Chart Type” option.

Select the combo chart and choose the Dividers series. Now change the chart type to “Scatter with Straight Lines ” and enable the secondary axis checkbox.