Home > Charts > Panel Chart

How to Build Excel Panel Charts

How to build a panel chart in Excel that shows two or more similar sets of data, side-by-side. Free workbook and step by step video

Introduction to Panel Charts

In an Excel panel chart, you can show two or more similar sets of data, side-by-side.

On this page, there are instructions for building a Line Panel Chart.

• Chart has product sales data for 2 products, and 4 cities
• Data has date range of 7 months - January to July.
• Vertical lines separate panels
• Easy to see sales for each city, and compare city sales

Further down on this page, you'll find information about an Excel chart add-in that quickly creates dot plot panel charts and bar panel charts. If you frequently need to build panel charts in Excel, this add-in could save you lots of time.

Create a Line Panel Chart

In Excel, there's no built-in Panel Chart type, but you can create line panel charts, like the one shown below.

This chart has product sales data for four cities, over 7 months, with the line charts in the four panels, arranged horizontally. This is based on a technique that Jon Peltier posted, and I have outlined the steps below.

In the next section, my step by step video shows how to create this line panel chart. There are written steps and screen shots, below the video.

Panel Chart Video

This video shows the steps for creating a line panel chart, using a Line Chart, and adding error bars and formatting, to separate the data into panels. The written steps are in the section below.

The full written transcript for the video is further down on this page.

Video Timeline

• 0:00 Introduction
• 0:24 Step 1 - Insert Column
• 1:11 Step 2 - Create Pivot Table
• 2:16 Step 3 - Copy Data As Values
• 2:48 Step 4 - Create Line Chart
• 3:31 Step 5 - Add Dividing Lines
• 7:06 Step 6 - Final Formatting

Why Use a Panel Chart?

A panel chart makes it easier to understand data from several categories, separated into smaller sections, that look like individual charts.

Without a Panel Chart

Without a panel chart, a chart with product sales data for four cities, over 7 months, is difficult to read.

For example, here's a pivot chart, with product sales data from 4 cities.

• pivot table summarizes the data
• pivot chart was created from the pivot table data

Unfortunately, it's almost impossible to read this chart, because it has a jumble of lines, in multiple colours.

Change Pivot Chart Layout

To create a less cluttered pivot chart, you could try a different layout in the pivot chart, and move City to the outer row area.

Here is the revised pivot chart, and this improved layout does make it easier to see the sales for the two different products - Bars and Cookies.

No Separation for Cities

However, there is no break between the cities in this revised pivot chart. The line for the two products looke like a single date range, rather than four distinct ranges - one for each city..

Unless you study the chart carefully, it's not clear that this line chart represents data from four cities.

Create a Panel Chart

To separate the four cities into separate "chunks", you can follow the steps in the next section and create a panel chart.

• The technique starts with a pivot table, to summarize the data.
• Then, instead of a pivot chart, which as some limitations, you'll build a normal Line chart from the summarized data
• Next, you'll make changes to the line chart, which will separate the data visually, into a panel for each city's product sales.

Panel Chart Steps

The instructions for making a panel chart in Microsoft Excel might look long, and a bit complicated, but I've grouped the instructions into the following 6 main steps:

Step 1 -- Add a Separator Field

Step 2 -- Summarize the data

Step 3 -- Copy the pivot table data

Step 4 -- Create a line chart

Step 5 -- Create vertical dividing lines

Step 6 -- Finish the chart formatting

There is an overview of the steps below, and you can see them in the video.

Step 1: Add Separator Field

In the sample Excel file used in the video, the data is in a formatted Excel table, on a worksheet named Sales_Data.

• The table has 8 columns - OrderDate, Category, Product, Regon, City, Qty, UnitCost and TotalPrice
• There are 421 rows of data, with 2 products, sold in 4 cities, over a 7-month period

New Column in Source Data

The secret to creating panels in a line chart, is to "stagger" the data, so it's broken into two columns.

To make this possible, follow these steps to insert a new column (field) in the source data:

• Right-click on the column F button, above the Qty heading in cell F1
• In the pop-up menu that appears, click on the Insert command
• The new column is inserted in column F, and the Qty column shifts to column G
• In cell F1, type a heading for the new column - Stagger

Add Stagger Numbers

There are four cities in this source data, and I'll divide them into two groups, using the numbers 1 and 2.

To decide on the numbering, I listed the cities alphabetically, and alternated the numbers 1 and 2 down the list.

• 1 - Boston and New York
• 2 - Los Angeles and San Diego

To add numbers in the Stagger column, follow these steps:

• In the City column heading, click the drop down arrow
  • Select New York and Boston, and remove the check mark for the other cities
  • Click at the top of the Stagger column headding cell, to select all the cells, but not the heading cell
  • Type the number 1 in the active cell, then press Ctrl+Enter, to fill all the selected cells
• Next, go back to the City column heading, and click the drop down arrow
  • Select New LosAngeles and San Diego, and remove the check mark for the other cities
  • Click at the top of the Stagger column headding cell, to select all the cells, but not the heading cell
  • Type the number 2 in the active cell, then press Ctrl+Enter, to fill all the selected cells
• Finally, go back to the City column heading, and click the drop down arrow
  • Click on the Clear Filter from City command, to remove the filter from the source data table

Later, in the pivot table, these numbers can be used to break the data into separate columns.

Step 2: Summarize the data

The next step is to summarize the data in a pivot table, to calculate the sales amount for each product and city, for the seven months of data.

To create the pivot table, follow these steps:

• Click on any cell in the source data table
• On the Excel Ribbon, click the Insert tab
• At the left end of the tab, in the Tables group, click the top part of the Pivot Table command
• In the PivotTable from table or range dialog box:
  • table name shows in the Table/Range box
  • for location, select New Worksheet
  • do not check the option to Add this data to the Data Model
• Click OK to create the pivot table on a new sheet

Add Fields to Pivot Table Layout

Next, add the following fields to the pivot table layout on the worksheet.

Dragging the fields in the PivotTable Field List, as shown in the screen shot below.

1) Rows Area: City, and OrderDate

• NOTE: If OrderDate is grouped automatically, press Ctrl+Z right away, to ungroup the dates

2) Columns Area: Stagger, and Category

3) Values Area: TotalPrice

Staggered Data for Cities

In the pivot table, thanks to the Stagger field, the City data appears in two sections, with gaps in the data.

• Those empty cells will not be plotted in the line chart
• Those data gaps will create breaks between the cities.

Pivot Table Changes

Next, change a few things in the pivot table layout, to get the data ready to use for the line chart

Subtotals and Grand Totals

• Right-click on one of the City names
  • In the pop-up menu, remove the check mark from Subtotal City
• (optional) Right-click on one of the Category names (Bars or Cookies)
  • In the pop-up menu, add a check mark for Subtotal Category
• Right-click on the Column Grand Total heading
  • Click on Remove Grand Total
• Right-click on the Row Grand Total heading
  • Click on Remove Grand Total

Report Layout

Finally, be sure your pivot table is in Tabular form

• Select any cell in the pivot table
• On the Ribbon, click the Design tab
• In the Layout group, click the arrow for Report Layout
• Click on Show in Tabular Form

Tabular layout puts each row field in a separate column, and the City name is on the same row as the first sales amounts.

Here is the revised pivot table, after the layout changes.

Step 3: Copy Pivot Table Data

Pivot charts are quick and easy to make, and they update automatically, if the pivot table changes.

However, pivot charts have some limitations, such as:

• not allowing scatter graph chart type
• not including the total amount.

We need those features to make an effective panel chart, so we'll use a copy of the pivot table data, and build a normal line chart, instead of a pivot table line chart.

Make Copy of Pivot Table Data.

To create a more flexible chart, follow the steps below, to make a copy ofthe pivot table data

• In the pivot table, select from the the City heading down to the last cell with data
• On the Excel Ribbon's Home tab, click Copy (or, use the keyboard shortcut, Ctrl+C)
• Insert a new worksheet
• On the new sheet, right-click cell A1, then click Paste Special, in the pop-up menu.
• In the Paste Special dialog box, select Values and Number formats, and click OK.
• Remove the City and Order Date headings
• Type Total in cells E1 and I1

Step 4: Create Line Chart

Next, to create the line chart, follow these steps:

• On the new sheet, select all the heading cells and data cells (include the blank headings and blank data cells)
• On the Ribbon's Insert tab, click Line Chart, and create a 2d line chart

A Line chart is added to the worksheet, with two sets of data for bars, cookies, and total, and they're formatted with different colors.

To make the colours consistent, format the second set of colours, to match the first set's colours

• First, on the chart, click on the legend to select it
• Next click on the 2nd Bars line, to select that item.
• Change its colour, so it matches the first bars.
• Repeat those steps, to change the colours for the 2nd Cookies, and the 2nd Total

Then, after all items in the 2nd set have been changed, you can delete the second set from the legend.

• Click on the 2nd Bars line in the legend
• Press the delete key, to remove it from the legend
• Repeat those steps, to remove lines for the 2nd Cookies, and the 2nd Total

Step 5: Create Vertical Dividing Lines

To finish the panel chart, we'll add another series to the chart. It will have vertical error bars that will separate the data for the four cities.

Create Dividing Line Data Table

To create the data for the dividing line series, follow these steps:

• Each city has 7 months of data, so type a 7 at the top of the sheet, in cell T1
• There are 4 cities, so 3 dividing lines are needed - after City 1, City 2, and City 3
  • Type those labels in cells S5, S6 and S7
• Type the following column headings:
  • T3: x
  • U3: y
  • V3: Err Bar
  • U4: Vert Grid
• The first dividing line will be after the 7th data point, midway between points 7 and 8.
  • Enter this formula in cell T5, to calculate where the first dividing line occurs:
• =T4+$T$1+0.5
• Copy that formula down to T6 and T7
• This series will run along this horizontal axis, so type a zero in cells U5, U6 and U7.
• Finally, the Error bars should all be the same height, so type a 1 in cells V5, V6 and V7.

Add Dividing Line Series to Chart

To add the dividing line series to the chart, follow these steps:

• Select cells T4 to U7 (green cells in the screen shot)
• To copy the selected cells, press Ctrl+C
• Next, click on the panel chart, to select it
• After that, go to the Home tab on the Excel Ribbon
• Next, click the arrow for Paste and click Paste Special.
• In the Paste Special dialog box, select these settings:
  • New series
  • Values in Columns
  • Series Names in First Row
  • Categories (X Labels) in First Column
• Click OK, to add the series

The new series (Vert Grid) appears as a short line, at the bottom left of the chart.

Change Chart Type

The dividing line series has been added to the panel chart, and now it needs to be formatted.

First, follow these steps to change the chart type:

• Right-click on the new series line
• In the pop-up menu, click on Change Series Chart Type
  • Change Chart Type dialog box opens
• In the list at the bottom, scroll down to find the Vert Grid series
• In the Chart Type drop-down list, click the arrow
• In the X Y (Scatter) section, click on Scatter with Straight Lines
• Click OK, to apply the chart type change

Secondary Axis

Next, follow these steps to put the Vert Grid series on a Secondary axis.

• Dividing line series (Vert Grid) is now a longer line at the bottom of the chart.
• Right-click the Vert Grid series line, and click Format Data Series
• In the Format Data Series pane, click the Series Options icon (small column chart)
• Under Series Options, click Secondary Axis

The Secondary Axis appears at the right side of the panel chart, with numbers from zero to one.

With Vert Grid series on this axis, the error bars, which have a height of 1, will be visible.

Add Error Bars

Next, follow these steps, to add the error bars, which will act as dividing lines.

• First, click on the new series line (Vert Grid), to select it
• On the Excel Ribbon, click the Chart Design tab
• At the left, in the Chart Layouts group, click the drop-down arrow for Add Chart Element
• In the drop-down list, click Error bars, and click More Error Bar Options.
• In the Format Error Bars pane, under Vertical Error Bar, choose the following settings:
  • Direction - Plus
  • End Style - No Cap
  • Error Amount - Custom, then click Specify Value
  • In the Custom Error Bars dialog box, click in the Positive Error Value box
  • On the worksheet, select blue cells, V5 to V7,, where error bar values (1) are entered
  • Click OK

Adjust Secondary Axis

Now the panel chart has vertical dividing bars. However, the dividing lines don't reach all the way up to the top of the chart, because the lines go up to 1, and the secondary axis goes up to 1.2.

To make the error bars reach the top, follow these steps, to adjust the secondary axis:

• Right-click on the Secondary Axis, and click Format Axis
• In the Format Axis pane, choose the following settings:
  • Minimum - 0
  • Maximum - 1
  • Major Tick Mark Type - None
  • Labels - None

After that change, the error bars reach the top grid line in the panel chart.

NOTE: The secondary axis is still here, but everything on that axis is hidden

Format Vert Grid Line

In these final few steps, you'll format the blue Vert Grid line, at the bottom of the panel chart.

Follow these steps to hide the blue line:

• Right-click the Vert Grid line, and click Format Data Series
• Go to the Fill & Line tab (paint bucket icon),
• In the Line section, click No Line

After the blue line is hidden, you can see short horizontal bars at the bottom of the dividing lines. Those are X error bars, and they aren't needed

Follow these steps to remove the X error bars, at the bottom of the dividing lines:

• Click on one of the horizontal X error bars, to select them
• Next, press the Delete key, to remove all 3 of the selected lines

Step 6: Finish the Formatting

Finally, here are a few optional steps you can do, to finish the chart formatting, so everything looks clean and clear.

Date Format

• In the panel chart's source data, select the dates
• Format the dates, so only the first letter of the month is showing

Gridlines

• Change the chart gridlines to a lighter colour
• Space the gridlines further apart

Title and Legend

• Remove the default chart title, or change it to something meaningful
• Move the legend to the top of the chart
• Remove the Vert Grid legend entry

Excel Add-in for Panel Charts

In addition to the line panel chart, shown above, there are other types of panel charts that you can create. For example, the screen shot below shows a bar panel chart, and it has bars with sales data for four cities.

• Each section of the panel is labeled with the city name
• you can see all the data in a small amount of space.

I built this chart using the Panel Chart feature in Jon Peltier's Excel Charting Utility add-in.

Create a Dot Plot or Bar Panel Chart

After you purchase and install the Charting Utility add-in, it's easy to create a panel chart (and many other types of complex Excel charts).

• First, select your data, then click a few buttons, and your chart is instantly created.
• Add a bit of formatting, if you prefer different colours, like the orange dots below.

Helpful Features: The add-in automatically adds a chart typle list box to the worksheets, so you can quickly switch between dot plot and bar panel charts.

Video Transcript: Create a Line Panel Chart

This is the full written transcript for the How to Create a Panel Chart in Excel video, shown above.

'---------------------------------------

Introduction

In an Excel panel chart, you can show two or more similar sets of data side by side.

In this chart, we have:

• four cities
• showing seven months of data for each city

They're in a single Excel chart, but it looks like four separate charts arranged horizontally.

In this tutorial, we'll see the steps for creating a line panel chart like this one, and we'll follow these six main steps.

Step 1 - Insert Column

Our first step will be to add a separator field to the source data.

Here's the data table, here's the city field and we have four different cities.

I've listed them here, alphabetically, and we're going to number them in two groups. I've put Boston and New York as one and San Diego and Los Angeles will be two.

I'm going to insert a column. So I'll right-click and Insert. I'm just going to select New York and Boston, type a one in those cells.

And then the other two cities will be number two.

Now the cities are numbered and I'm going to name this column stagger, because we'll use it to stagger the data.

So instead of it appearing in one column, when we create a pivot table, it will be in columns one and two that will create some blank areas in the data and those blanks won't be plotted in the chart.

Step 2 - Create Pivot Table

The next step in creating a panel chart is to summarize this data in a pivot table.

• Click on any cell in the data and on the Insert tab, click Pivot Table.
• This table where the data is stored is Table1.
• We want this on a new worksheet and click OK.
• And there's our blank pivot table.

I'm going to put City and order date in the row labels area. We'll put total price in the values and across the top we want Stagger.

• So I'll drag that into the column labels
• And the other field we want there is Category and I'll drag that below Stagger.
• I'm going to get rid of these city subtotals. I'll right-click on Boston and remove the check mark from subtotal city.

I also want the layout a little different, so that city and order date are in separate columns

• On the design tab where we are, there's a Report Layout
• Right now, we're in Compact form, which is the default. I'm going to go with Tabular form.
• We also have grand totals in this pivot table and I'm going to remove those because we don't need them. So click Grand totals off for rows and columns.

Step 3 - Copy Data As Values

We're going to as the next step, copy this data and put it onto another sheet, pasted as values

I've zoomed out so we can see the entire pivot table.

• I'm going to select from the city heading down to the last cell with data
• And on the Home tab, click Copy
• And on a new, sheet click where I want it to be pasted, click the arrow for Paste.
• And we want to paste values and number formatting.
• I'm going to put the word Total in these two headings
• And remove the city and order date headings.

Step 4 - Create Line Chart

The next step will be to create a line chart from this data that we've copied

• On the Insert tab, click Line and create a 2d line chart

There we can see two sets of data with bars, cookie, and total, and they're formatted with different colors.

We want them all to appear to be the same set of data. So I'm going to change the formatting on the second set.

• I've clicked to select the legend
• And then I'll click on bars and that selects that item.
• I'm going to change its formatting, so it matches the first bars.

Once they're all formatted in the same color, you can delete the second set from the legend.

• With that item selected. I'll just press the delete key and it's removed from the legend.

Step 5 - Add Dividing Lines

The next step is adding the vertical dividing lines between the city data

• I've created a table on the worksheet.
• I've got three rows, one that will be used for each vertical line.
• We have data from January to July.
• So we have seven data points in each city set
• The first dividing line we want after the seventh data point, midway between that and eight. So it will be at this number plus 0.5.
• Each one that follows will be seven points after preceding one.
• We want this series to be at the zero point, so it will run along this horizontal axis.

We're also going to create error bars and they'll all be the same height, and we've set that at one

• To add a series to the chart, we're going to select the green cells and copy them.
• So with Ctrl + C, I copied, then I'll select the chart.
• And on the Home tab, I'll click the arrow for paste and click paste special.
• I'm going to paste this as a new series in columns names in the first row categories in the first column and click OK

It's been added to the chart. We just see a little blue line here. We're going to change this into a scatter chart.

• So I've selected that little blue line
• And on the Design tab, change chart type X, Y scatter
• And I'll select a line with no markers and click OK.

Now this has automatically added a secondary axis and our line is now longer across the bottom.

• I'm going to click on it to select it.
• And if you have any trouble selecting it, you can go to the Layout tab, and there's a dropdown where you can select any item. So I could select series vertical grid.
• So with this selected, I'm going to add error bars
• On the Layout tab, click Analysis, Error bars, and click More error bar options.
• We are going to put in vertical error bars. We want plus no cap and a custom setting here.
• I'm going to move this so that I can see the cells that have the one, then click specify value.
• And for the positive error values, select the three blue cells that have the one in them and click OK, and then close this window.

We now have the vertical bars. They don't reach all the way up to the top though, because they go up to one and this axis goes up to 1.2.

• So click on the axis and on the Layout tab, click Format Selection
• For this, we want the minimum to be zero, which we can see in there. We could fix that at zero
• Maximum we'll fix at one.
• We also want to hide the marks that are on that secondary axis.
  So for Major Tick Mark Type, put None, this is already set to None.
• The labels we'll put to None, and click OK

So the secondary axis is still here, but everything is hidden.

• We don't need this secondary horizontal axis. It's causing these vertical lines to appear in the wrong spot because they're on that secondary axis.
• I will select that and press Delete.

Now these vertical lines are on the primary axis and lined up correctly.

We still have this blue line at the bottom.

• So I'm going to select that series and click format selection
• The line color, we want No Line, and click Close.

So I can see little marks at the bottom of these vertical lines. So there is an X error bar there as well.

• If I click and select the X error bars
• When they're highlighted, I can press the Delete key and get rid of those as well.

Step 6 - Final Formatting

And the final thing we'll do is a little formatting to make these grid lines look a little nicer. They're quite dark and narrowly spaced.

• So if I double click on one of those, I get the major grid lines.
• I'll select solid line, a light grey

For the axis, I format that.

• Right now, the major unit is 5,000. I can make that 10,000 instead. So they're a little better spacing.

And finally, to make these dates look better, I'll have to go to the source data and format them.

• Here are the dates.
• On the Home tab, I'll click the Dialog Launch button
• Number tab, Date
• And I'll pick this single M which is going to give me just the first letter of the date.
• Click OK

So now we just have a single letter there and in the chart it's been picked up automatically.

It's just showing every second one though.

• So if I select that axis, and format it
• Instead of Automatic Interval, I'll click Specify Interval, and set that at one.
• When I click close, now there's a letter for each month

And I'm going to, as my last thing, just take this grid out of the logo, and our chart is ready

Panel Chart Sample File

Line Panel Chart: To see the sample data, and the completed line panel chart, you can download this panel chart sample file. The zipped file is in xlsx format, and does not contain macros.

More Chart Links

Charts, Cluster Stack

Charts, Line-Column 2 Axes

Charts, Waterfall

Charts, Interactive

Interactive Chart - Totals