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.
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.
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.
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.
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.
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:
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.
To add numbers in the Stagger column, follow these steps:
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:
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
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.
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
Finally, be sure your pivot table is 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:
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
Step 4: Create Line Chart
Next, to create the line chart, follow these steps:
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
Then, after all items in the 2nd set have been changed, you can delete the second set from the legend.
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:
Add Dividing Line Series to Chart
To add the dividing line series to the chart, follow these steps:
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:
Next, follow these steps to put the Vert Grid series on a 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.
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:
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:
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:
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.
Title and Legend
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.
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).
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.
In an Excel panel chart, you can show two or more similar sets of data side by side.
In this chart, we have:
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.
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.
I also want the layout a little different, so that city and order date are in separate 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.
Step 4 - Create Line Chart
The next step will be to create a line chart from this data that we've copied
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.
Once they're all formatted in the same color, you can delete the second set from the legend.
Step 5 - Add Dividing Lines
The next step is adding the vertical dividing lines between the city data
We're also going to create error bars and they'll all be the same height, and we've set that at one
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.
Now this has automatically added a secondary axis and our line is now longer across the bottom.
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 the secondary axis is still here, but everything is hidden.
Now these vertical lines are on the primary axis and lined up correctly.
We still have this blue line at the bottom.
So I can see little marks at the bottom of these vertical lines. So there is an X error bar there 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.
For the axis, I format that.
And finally, to make these dates look better, I'll have to go to the source data and format them.
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.
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
Last updated: November 2, 2022 12:29 PM