How to Make Panel Chart in Excel – Video Step-by-Step

In Excel, there’s no built-in Panel Chart type, but you can create line panel charts, like the one shown below. Watch a video, to see the steps, and there’s a transcript too, and a link where you can download the sample Excel file.

Excel Panel Chart Example

This chart has product sales data for four cities, over 7 months, with the line charts in the
four panels, arranged horizontally.

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 full written transcript for the video is below the video.

There are detailed written steps on the Excel Panel Chart page on my Contextures site.

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

Get the Sample File

To follow along with the video, you can download the Excel file from the Excel Panel Chart page on my Contextures site..

The zipped file is in xlsx format, and does not contain macros.

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

_______________________