Cycle Plot Template

A Cycle Plot (panel chart or small multiples) is a small comparison chart that consolidates the charts using the same scale. Sometimes we are working with custom reports and need a space-saving solution. The cycle plot is a perfect chart to track and analyze data using a small space.

In today’s guide, we will take a closer look at the Cycle Plot. In the example, you will learn how to compare the sales performance of two companies using five years.

cycle plot excel template

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

How to create a Cycle Plot in Excel

Step 1: Prepare the Data

Because we want to show you every step, let’s start with the initial data set. 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:

data table for cycle plot

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

Step 2: Add separators to split line charts

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

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

create a helper column for dividers

Step 3: Create a new Pivot Table

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

create a simple 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

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

  1. Place the Product and Year to Rows.
  2. Move “Divider” to “Columns area.”
  3. Add the “Acme Corp.” and “Umbrella Corp.” to the “Values” field.
pivot table layout

Result:

pivot table result

To remove subtotals, Grand Totals, and simplify the pivot table, do the following steps. Select any cell in the pivot table and locate the Design Tab.

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

Our data set looks like on the picture below:

pivot table after cleanup

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

add headers to the table

Step 6: Insert line charts

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

insert a line chart for the cycle plot

Step 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 using right-click and choose “Outline.” In the example, we are using purple for the Umbrella Corp. and Orange for the Acme Corp.

add colors for the cycle plot

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

drop-down list for colors

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

create a helper table for dividers

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

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

Step 9: Create dummy series for cycle plot

Let’s see the key steps!

  1. Select and copy the helper table data
  2. Select the chart area.
  3. 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:

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

Step 10: Change the inserted Chart Type

In the picture below, you can see the inserted data series. Right-click on the line and select the “Change Series Chart Type” option.

change series chart type

Select the combo chart and choose the Dividers series. Now change the chart type to “Scatter with Straight Lines and make sure the secondary axis checkbox is enabled.

add a new chart type for cycle plot

Step 11: Clean up the chart and format axes

We need to remove the unnecessary elements. Select the secondary horizontal axis using right-click. Click Delete.

delete axis

Now, right-click on the vertical axis and use the “Format Axis.” option.

Use the following setup on the Task pane:

  1. Select the Axis Options tab
  2. Enter “0” for the Minimum Bounds
  3. Add “1” as a value for the Maximum Bounds
format right axis

Locate the Labels section and change “Label Position” to “None” to hide the axis scale.

label position none

Step 12: Use error bars as dividers

We are almost ready! Now we’ll add error bars for the cycle plot.

  1. Select Series “Dividers.”
  2. Click the plus sign to reach the Chart Elements menu.
  3. Select “Error Bars, then click “More Options.”
use error bars as dividers

Change the vertical error bar settings in the Format Error Bars task pane to modify the dividers:

  1. Select “Plus” for direction.
  2. Apply “No Cap” for End Style
  3. Add “1” as a “Fixed Value” under Error Amount Group
format error bars

Step 13: Hide the dummy chart elements

Finally, clean up the chart.

Choose the ‘No line’ option for the series Dividers using the Format Data series Tab.

apply no line to hide the series

Delete Horizontal error bars!

Additional steps to improve the Cycle plot

  • Add a chart title
  • Remove duplicated company names from
  • Add smoothed lines

Here is the final chart!

cycle-plot-excel-template

Download the example and stay tuned.

Read more about excel chart templates!