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
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.
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.
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.
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.
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.
The instructions for making a panel chart look long and complicated, but we can group the instructions into the following main steps:
There is an overview of the steps below, and you can see them in the video.
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.
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.
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.
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.
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.
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).
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.
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:
Finally, fix the chart formatting, so everything looks clean and clear.
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.
To see the steps for creating a line panel chart, please watch this video tutorial.
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.
If you do lots of work with charts in Excel, save time with Jon Peltier's Excel Chart Utility.
Last updated: April 12, 2021 7:15 PM
Contextures RSS Feed