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.
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.
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.
Copy the pivot table data
After the pivot table is finished, copy the data, and paste it as formatted numbers on another worksheet.
Create a line chart
Using the copied data, create a line chart, and format the two sets of series so they look the same.
Create vertical dividing lines
Add another series to the line chart, and add error bars to create vertical lines between the cities.
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. 😉
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.