Panel Chart Template

Using a Panel chart (small multiples) is a great decision if you want to compare products or sales and show the data on the same scale. If you create dashboards in Excel, it is important to use space-saving methods and focus on the data.

Our step-by-step tutorial shows you how to create a panel chart from the ground up. Our goal is to compare the performance of two products using four locations.

Here is a great template that uses a panel chart:

final excel panel chart

One more important thing: Excel does not support the panel chart in default. We need to follow custom steps to build it. If you are in a hurry, use our chart add-in. With its help, you can create the chart in seconds.

Steps to create a Panel Chart in Excel

Step 1: Arrange your Data

Before we take a deep dive into the details, let us see our data:

In the example, we will compare the data sets of the two software, ‘Blue Lock’ and ‘Red Desk,’ using four different regions in five years. Create the following structure for the panel chart.

We will apply pivot tables to manage data.

arrange data example

It is worth using Pivot Tables for consolidation or data extraction! You can create the output manually, but it can take a while.

Step 2: Add a helper column to the table

The first trick is to add a helper column. It will play an important role if you are working with a Pivot Table. Add a new column in column G and enter ‘1’ for the range G4:G8 and G14:G18. Now use ‘2’ for range G9:G13 and G19:G23.

Add a helper column to the table

Step 3: Build a Pivot Table from the data

Steps to build a new Pivot Table:

  1. Select the range of cells, in this case, cell C3
  2. Click on the Insert Tab
  3. Click PivotTable.
create a pivot table

Now in the ‘Create PivotTable’ dialog box, apply the following settings. We want to manage the original data set and the new Pivot data in the same Worksheet. Use the ‘Existing Worksheet’ as a location. Click OK to close the window. In the example, place the new table to cell I2.

add the pivot table location

Step 4: Build a pivot table output using Fields

We have an empty Pivot Table. It is time to add data to the table. Click inside the Pivot Table, and add fields using the PivotTable Fields task pane. We need this structure to create the panel chart.

  1. Move the ‘Location’ and ‘Year’ to Rows.
  2. Drag ‘Dividers’ and drop to ‘Columns.’
  3. Add the ‘Blue Lock’ and ‘Red Desk’ to the ‘Values.’
Build a pivot table output using Fields

The table looks like this:

pivot table before transform data for the panel chart

The table contains redundant records, so we need to remove subtotals and transform the data. Select a cell in the Pivot Table range and select The Design Tab.

design tab
  1. Choose the Subtotals icon and remove subtotals using the ‘Do not show Subtotals.’
  2. Click on the ‘Grand Totals,’ select ‘Off for Rows and Columns.’ To hide Grand Totals
  3. Enable a custom view. Click on the Report Layout menu. Select ‘Show in Tabular Form.’
pivot table transformation

Our data looks much better!

pivot table is ready for panel chart

Step 5: Extract data and add a Header

Copy the Pivot Table into a new location using the Paste Special command and add a new row that contains the header.

final table

Step 6: Create line charts

To create a panel chart, we need to insert and combine line charts. Select the data and click on the Insert Tab. Insert a line chart by clicking the icon.

insert a line chart

Step 7: Change the data series colors

The line chart contains different colors for each series. Apply a consistent color scheme. We will use the same colors for the same series.

Right-click on the chart and select ‘Outline.’ In this example, we will use blue for the ‘Blue Lock’ and red for the ‘Red Desk.’ If you use the context menu and the drop-down list, it is easy to select the series.

Change the data series colors for panel chart

Step 8: Calculate the separator line positions

To improve the panel chart, we have to divide the line chart section into four parts. We will apply vertical error bars as separators. To plot the dividers, we’ll create a small table for additional calculations.

Calculate the separator line positions

In the example, we will use the range Y4:Z8

  1. Leave the cell Y5 blank, and fill the cells using zeros in column Z.
  2. Enter the following formula in cell Y6. =COUNTA(R5:R9)+0.5
  3. Apply the =Y6+COUNTA(R10:R14) formula in cell Y7.
  4. For the cell Y8, use the =+Y7+COUNTA(R11:R15) formula.

Step 9: Create temp series for Panel Chart

Follow the steps below to build a dummy series:

  1. Select and copy range Y5:Z8
  2. Click on the chart area.
  3. Go to the Home tab and select ‘Paste.’
  4. Use the ‘Paste Special’ command
Create temp series for Panel Chart

The ‘Paste Special’ dialog box will appear.

Make sure that the following checkboxes are checked:

  1. New series
  2. Columns
  3. Series Names in First Row
  4. Categories (X Labels) in First Column

Click OK to close the dialog box.

paste special options for series

Step 10: Use Scatter Plots with straight lines

You can find the new series at the bottom-left corner.

Right-click and apply the ‘Change Series Chart Type’ command.

Use Scatter Plots with straight lines

Select the Dividers series and apply a new chart type: ‘Scatter with Straight Lines.’

Scatter with Straight Lines

Step 11: Remove the unnecessary chart elements

It can take only seconds to clean up the chart.

First, Right-click on the secondary axis, then click ‘Delete.’

Remove the unnecessary chart elements

Right-click on the vertical axis!

Check the ‘Format Axis’ (Axis Options Tab) setup on the Task pane:

  • Add ‘0’ for Minimum Bounds
  • Add ‘1’ for Maximum Bounds
axis options for panel chart

To hide the axis scale, jump to the Labels section. Then, change ‘Label Position’ to ‘None.’

label position to none

Step 12: Use error bars as Panel Chart separator

The panel chart looks much better! Let us see how to add error bars.

  1. Select the series ‘Dividers’ at the bottom of the chart area
  2. Under the Chart Elements menu, click the plus sign
  3. Hover the mouse over the ‘Error Bars,’ then click ‘More Options.’
Use error bars as Panel Chart separator

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
Change the vertical error bar settings

Step 13: Clean up the Chart (Part II.)

The last step is to clean up the chart. Select the ‘Dividers’ series and click on ‘No Line.’ You can hide the horizontal error bars, too, using this method.

Clean up the Chart part 2

To improve your panel chart, enter a title and remove the duplicated product names from the legend. Then, add a shadow for the lines and/or use smoothed lines.

Here is the panel chart!

final panel chart template

If you want to learn more about chart templates, visit our guide.

Download the practice file.