How to Build Excel Panel Charts

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

Introduction to Panel Charts

In a panel chart, you can show two or more similar sets of data, side-by-side. In the next section, there are instructions for building a Line Panel Chart.

Another option is the bar chart shown below, that has sales data for four cities. Each section of the panel is labeled with the city name, and you can see all the data in a small amount of space.

report filter change vba

I built that chart using Jon Peltier's Panel Chart Utility (no longer available, replaced by his Excel Charting Utility). It even adds a list box to the worksheet, so you can quickly switch between dot plot and bar panel charts.

report filter change vba

Create a Line Panel Chart

You can also create line panel charts, like the one shown below. It has data for the same four cities, 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. There is also a video that shows how to create this line panel chart.

panel chart line finished

Panel Chart Video

To see the steps for creating a line panel chart, you can watch this video tutorial. The written steps are below.

Why Use a Panel Chart?

A panel chart makes it easier to understand data from several categories. For example, if we create a pivot chart from the summarized data, it's almost impossible to read, with a jumble of lines.

panel chart line start

To create a less cluttered pivot chart, we can move City to the outer row area. However, there is no break between the cities -- it appears to be a single date range, rather than four distinct ranges.

It's not clear that this line chart represents data from four cities. A panel chart would separate the data, so you could identify the different cities at a quick glance.

panel chart line city

Panel Chart Steps

The instructions for making a panel chart look long and complicated, but we can group the instructions into the following main steps:

Add a Separator Field

Summarize the data

Copy the pivot table data

Create a line chart

Create vertical dividing lines

Finish the chart formatting

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

Add a separator field

The secret to separating the groups in a panel chart is "staggering" the data in the pivot table. Instead of having all the data in a single column, it is broken into two columns, by adding a "Stagger" field in the source data.

There are four cities in this source data, and they were divided into two groups in the Stagger column -- 1 and 2.

panel chart stagger field

Summarize the data

After adding the "Stagger" field, create a pivot table from the data, with City and Order date in the Row area, Stagger and Category in the Column area, and Total Price in the Values area.

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, and that will create breaks between the cities.

panel chart line city

Before copying the pivot table data, I displayed the subtotals in the columns, so I could include those totals in the chart. You can leave subtotals hidden, if you don't want totals in your chart. I removed the Grand Totals from the pivot table.

Copy the pivot table data

After the pivot table is finished, copy the data, and paste it as Values, with number formatting, on another worksheet.

Pivot charts are quick and easy to make, and they update automatically, if the pivot table changes. However, they have some limitations, such as not allowing scatter graph chart type, and not including the total amount.

To create a more flexible chart, you can copy the pivot table data, and paste it as values, on a different sheet. In the next step, we'll create a new chart, based on the pasted data. This won't update automatically, but it is a good solution after your data has been finalized.

Add headings in the Total columns, and delete the headings for City and Order Date.

panel chart pasted data

Create a line chart

Using the copied data, create a line chart. It will have two sets of series named, Bars, Cookies and Total. Format the two sets of series so they look the same, then delete one set from the Legend.

  • Click on the Legend to select it, then click on a Legend item, and press the Delete key.

panel chart with totals

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.

A small table is set up, with the series data for an XY (scatter) graph. The points on the X axis will be centred between the cities. There are 7 dates for each city, so the first vertical line will appear between the 7th and 8th data point -- at 7.5. There are four cities, so the table calculates where each of the 3 dividing vertical lines will appear.

The Y axis is set to zero, because it will sit on the horizontal axis. The error bars will be set at 1, and that number is entered in each row of the table's third column (blue cells).

panel chart error bar data

The green cells are copied, and pasted into the chart as a new series, with series in columns, series names in the first row, and category labels in the first column. The new series is changed to a scatter (XY) chart type -- straight line with no markers. Excel automatically places the series onto secondary X and Y axes, which are added to the chart.

Then, Y error bars are added to the series, and the secondary Y axis is set to a maximum of 1.

panel chart vertical lines

The scatter graph is formatted to hide the line, and the X error bars are deleted, so only the vertical error bars show.

To change the series so it is connected to the original X axis, and so the vertical lines will fall in the correct positions:

  • On the Ribbon, click the Layout tab, and click Axes
  • Click Secondary Horizontal Axis, and click None

panel chart error bar data

Finish the Formatting

Finally, fix the chart formatting, so everything looks clean and clear.

  • dates are formatted in the source data, so only the first letter of the month is showing

    date format M

  • gridlines are set to a lighter colour, and spaced further apart
  • the secondary axis is hidden

panel chart line finished

Panel Chart Utility

UPDATE: The Panel Chart Utility is no longer available -- see Jon's Excel Charting Utility

To create dot plot and bar panel charts, quickly and easily, you can buy Jon Peltier's Panel Chart Utility. Select your data, 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. A list box is automatically added to the worksheets, so you can quickly switch between dot plot and bar panel charts.

panel chart utility

Panel Chart Sample File

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

Excel Chart Utility

If you do lots of work with charts in Excel, save time with Jon Peltier's Excel Chart Utility.

Peltier Tech Charts for Excel 3.0

More Chart Links

Jon Peltier's Toolbox Add-in

Charts, Cluster Stack

Charts, Line-Column 2 Axes

Charts, Waterfall

Charts, Interactive

Interactive Chart - Totals

Last updated: July 14, 2021 3:24 PM