How to Create a Panel Chart in Excel

To show a concise, clear summary of data for several departments or cities, you can create a panel chart in Excel. It shows all the data in a single chart, with vertical lines separating the groups.

My chart shows sales for bars and cookies, in four cities, over the first 7 months of the current year.

panelchartline11

I learned this technique from Jon Peltier’s website, where he also sells a Panel Chart Utility, that creates dot plot and bar panel charts.

Panel Chart Steps

The instructions for making a panel chart look long and complicated, and I’ve avoided learning this technique, because it was a daunting process.

Last week, I finally took the plunge, and it’s not so bad, once you get the big picture in your head. We can group the instructions into the following main steps:

  • Add a separator field to the source data
  • Summarize the data in a pivot table
  • Copy the pivot table data as values
  • Create a line chart from the copied data
  • Add another series to create vertical dividing lines
  • Add final formatting to clean up the chart

There is an overview of the steps below, and you can see the detailed instructions on my Contextures website: Excel Panel Charts

Or, follow along as I build a line panel chart in the video, further down in this article.

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.

panelchartline04 

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 Sales in the Values area.

panelchartline07

Copy the pivot table data

After the pivot table is finished, copy the data, and paste it as formatted numbers on another worksheet.

panelchartline07 

Create a line chart

Using the copied data, create a line chart, and format the two sets of series so they look the same.

panelchartline06 

Create vertical dividing lines

Add another series to the line chart, and add error bars to create vertical lines between the cities. panelchartline10 

Clean up the chart formatting

Finally, fix the chart formatting, so everything looks clean and clear. The dates are formatted to show a single letter, gridlines are faded and further apart, the secondary axis is hidden, and a few others things were fixed. In Jon’s version of the chart he moved the city names to the top, but I was too tired when I got to that step! I think they look fine at the bottom. ;-)

panelchartline11

Watch the Panel Chart Video

To see the steps for creating a line panel chart, please watch this video tutorial.

Download the Sample File

To see the sample data, and the completed chart, you can download the sample file from my Contextures website: Excel Panel Charts. The detailed instructions are also on that page.

___________________

You may also like...

4 Responses

  1. Salman says:

    please give sample file or tamplate for small scale business monthly payroll

  2. rajinikanth says:

    hi….Very good chart

  3. Maxim Manuel says:

    Great tips. Thanks again!

  1. September 4, 2012

    […] How to create a Panel Chart […]

Leave a Reply

Your email address will not be published. Required fields are marked *