How to Create a Panel Chart in Excel

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on October 30, 2023

This tutorial will demonstrate how to create a panel chart in all versions of Excel: 2007, 2010, 2013, 2016, and 2019.

Panel Chart – Free Template Download

Download our free Panel Chart Template for Excel.

Download Now

A panel chart (also called a trellis chart or a small multiple) is a set of similar smaller charts compared side-by-side and divided by separators. Since these mini charts share the same axes and are measured on the same scale, basically, a panel chart consolidates all of them into one place.

Not only does the chart allow you to neatly display more information, it also helps you quickly compare or analyze the relationship between multiple data sets at once—while saving you a great deal of dashboard space.

As an example, take a look at the panel chart below, which combines four separate line charts comparing the annual revenue of Orange Ltd and Banana Ltd franchisees across four states for the past six years.

Panel chart in Excel free template

As you can see, the panel chart illustrates a bottomless well of useful data, as opposed to the jumble of lines you would end up getting if you simply opted for its built-in counterpart.

Unfortunately, this chart type is not supported in Excel, which means you will have to manually build it yourself. But before we begin, check out the Chart Creator Add-in, a versatile tool for creating advanced Excel charts and graphs in just a few click.

In this tutorial, you will learn how to plot a customizable panel chart in Excel from the ground up.

Getting Started

To illustrate the steps for you to follow, we need to start with some data. As you may have already guessed, we are going to compare the historical track record of the franchisees of two companies, Orange Ltd and Banana Ltd, in four different states—Iowa, Minnesota, Texas, and Utah.

With all that said, consider the following table:

Panel chart original data

Let’s cover each column more in detail.

  • State – This column represents the categories by which the chart will be split into smaller charts (panels). In our case, every mini line chart illustrates the performance dynamics for each of the states.
  • Year – This column determines the horizontal axis scale. The values and formatting should be identical across all panels.
  • Orange Ltd and Banana Ltd – These are your actual values. We are going to use just two sets of data for illustration purposes, but with the method shown in this tutorial, the sky is the limit.

In the end, we want our data to look like this:

Manually manipulate you raw data

Technically, you can manipulate the data manually (if so, skip to Step #5). This approach may be preferable if your data is simple. Otherwise, we will show you how to use a pivot table to manipulate the data into the necessary format.

So, let’s dive right in.

Step #1: Add the separators.

Before you can create a panel chart, you need to organize your data the right way.

First, to the right of your actual data (column E), set up a helper column called “Separator.” The purpose of this column is to split the data into two alternating categories—expressed with the values of 1 and 2—to lay the groundwork for the future pivot table.

Type “1” into each corresponding cell of column Separator that belong to the first category, Iowa (E2:E7). Then type “2” into all the respective cells that fall into the second category, Minnesota (E8:E13).

In the same way, fill the remaining blank cells in the column, alternating between the two separator values as you move from state to state.

Add the separators

Step #2: Add a pivot table.

Once you have completed Step #1, create a pivot table.

  1. Highlight any cell within the dataset range (A1:E25).
  2. Go to the Insert tab.
  3. Choose “PivotTable.

Add a pivot table

When the Create PivotTable dialog box appears, select “Existing Worksheet,” highlight any empty cell near your actual data (G1), and click “OK.

Create PivotTable

Step #3: Design the layout of the pivot table.

Immediately after your pivot table has been created, the PivotTable Fields task pane will pop up. In this task pane, shift the items in the field list into the following order—the order is important—to modify the layout of your new pivot table:

  1. Move “State” and “Year” to “Rows.
  2. Move “Separator” to “Columns.
  3. Move “Orange Ltd” and “Banana Ltd” to “Values.

Design the layout of the pivot table

At this point, your pivot table should look something like this:

Pivot table

Now, change the layout type and remove the redundant table elements.

  1. Select any cell in the pivot table (G4:L26).
  2. Navigate to the Design tab.
  3. Click “Report Layout.
  4. Choose “Show in Tabular Form.

Show in Tabular Form

Still in the Design tab, click the “Grand Totals” icon and pick “Off for Rows and Columns” from the dropdown menu.

Off for Rows and Columns

Next, click “Subtotals” and choose “Do Not Show Subtotals.

Do Not Show Subtotals

After you have followed all the steps, your pivot table should be formatted in this manner:

A modified pivot table

Step #4: Extract the data from the pivot table.

Since you don’t want to build a pivot chart (no, you don’t), it’s time to separate the data from the pivot table. Copy all the values from your pivot table (G4:L27) into the empty cells beside it. (An empty column between the sets of data will help avoid confusion.)

Extract the data from the pivot table

The header row (P3:S3) of your newly-created table is responsible for displaying chart legend items, so let’s add them quickly as well.

Since the pivot table effectively split the data into two quasi-line charts, your header row should go as follows:

Add chart legend items

Step #5: Create a line chart.

Finally, plot a regular line chart and see what happens.

  1. Highlight the table containing the data extracted from the pivot table (N3:S27).
  2. Go to the Insert tab.
  3. Click the “Insert Line or Area Chart” button.
  4. Choose “Line.

Create a line chart

Step #6: Make the line colors consistent.

As you can see, Excel created a line chart with four different data series. Unify that data by using the same colors for each company.

Right-click on any data series and choose “Format Data Series.

Format Data Series

In the Format Data Series task pane, do the following:

  1. Switch over to the Fill & Line tab.
  2. Under “Line,” select “Solid Line.
  3. Click the “Outline color” icon and pick your color from the palette. (Repeat for each line.)
  4. Double-check the chart legend to ensure you have matched the colors the right way.

Make the line colors consistent

Step #7: Create the dividing lines.

The task at hand may seem like nothing special, but it is not as easy as it sounds. Yes, you could simply draw the lines using built-in shapes, but they would end up being misplaced every time you resize the chart.

So, we are going to use vertical error bars instead, which will be positioned between each panel, right where the Y axis crosses the category axis.

Panel chart with error bars

However, to work around the issue, you will have to jump through a few hoops. First, let’s create the helper chart data:

  1. Set up a separate dummy table the exact same way as you see it on the screenshot below. Leave cell U4 empty to make things work and type “Dividers” into V4.
  2. Type “0” into V5 and copy it down. The number of rows containing values in the table represents how many error bars will be eventually created. In this case, we need three error bars, so there will be three cells with “0” in them.
  3. Type “=COUNTA(O4:O9)+0.5” into U5.

The formula calculates the number of x-axis scale values used for one panel and calculates its midpoint to position the lines on the X axis right where the two axes meet.

Create the helper chart data

Now, type “=U5+COUNTA(O4:O9)” into U6 and copy it down into U7.

Compute the x-axis values

Once you have set up the helper table, insert that data into your panel chart.

  1. Highlight all the values in the helper table, except for the header row (U4:V7).
  2. Select the chart area.
  3. Go to the Home tab.
  4. Click “Paste.
  5. Choose “Paste Special.

Insert the chart data into your panel chart

In the dialog box that appears, follow a few simple steps:

  1. Under “Add cells as,” select “New series.
  2. Under “Values (Y) in,” choose “Columns.
  3. Check the “Series Names in First Row” and “Categories (X Labels) in First Column” boxes.
  4. Click “OK.

Paste Special dialog box

Step #8: Change the chart type of the dummy series.

Right-click on the newly-added data series (Series “Dividers”) and choose “Change Series Chart Type.”

Change Series Chart Type

For Series “Dividers,” change “Chart Type” to “Scatter with Straight Lines.

Change the chart type of the dummy series

Step #9: Modify the secondary axes.

First, remove the secondary horizontal axis. Right-click on the numbers along the top of the chart and choose “Delete.

Remove the secondary horizontal axis

Then, right-click on the secondary vertical axis along the right side of the chart and select “Format Axis.

Modify the secondary vertical axis

In the Format Axis task pane, set in stone the axis scale ranges:

  1. Switch to the Axis Options tab.
  2. Change the Minimum Bounds value to “0.
  3. Set the Maximum Bounds value to “1.

Change the axis scale ranges

In the same tab, scroll down to the Labels section and change “Label Position” to “None” to hide the axis scale—if you simply delete it, that will ruin the chart.

Hide the secondary vertical axis scale

Step #10: Add error bars.

The groundwork has now been laid, meaning you can finally add the error bars.

  1. Select Series “Dividers.”
  2. Click the Chart Elements icon.
  3. Click the arrow next to “Error Bars.”
  4. Choose “More Options.

Add error bars

In the Format Error Bars task pane, modify the separators.

  1. Switch to the Error Bar Options tab.
  2. Under “Direction,” choose “Plus.
  3. Under “End Style,” select “No Cap.
  4. Under “Error Amount,” change “Fixed value” to “1.

Modify the error bars

Step #11: Hide the helper chart elements.

As the final step, remove the underlying secondary scatter plot, horizontal error bars, and duplicate legend items.

Let’s deal with the dummy series first. Select Series “Dividers,” navigate to the Format Data Series task pane, and in the Series Options tab, choose “No Line.

Hide the scatter plot

Now, remove the horizontal error bars and every legend item save for “Orange Ltd” and “Banana Ltd” by selecting each item, right-clicking, and choosing “Delete.” By the end of this step, your panel chart should look like this:

Hide the helper chart elements

Change the chart title, and you have your stunning panel chart ready to go!

Panel chart in Excel free template

Download Panel Chart Template

Download our free Panel Chart Template for Excel.

Download Now

AI Formula Generator

Try for Free