Guest Post: A Step-by-Step Guide to Creating a Panel Chart in Excel

(Note: This guest post is from Puneet Gogia at ExcelChamps.com. Puneet is a good friend of the blog, and today he shares how to build a panel chart in Excel. As I mentioned in my segment on the MyExcelOnline podcast, my goal is to improve in data visualization this year. Take it away, Puneet!)

In Excel, we have a number of ways to compare data. Charts are the top most out of them.

Selecting the right type of a chart can be challenging.

Just look at the below chart where I am trying to compare a trend in salary across six different departments. The chart is hardly readable.

So do you think I have overloaded this chart with the data?

Well, I am a super believer of using interactive charts in excel. They can help you to present more data in a single chart. But again, the comparison is still far away.

So, the one thing you can use to easily create a comparison is to use a Panel Chart. Yes, a Panel Chart.

The single core motive of panel chart is to help you to compare data in a single sight. Look at this panel chart below, where you can easily compare salary trend for all the 6 departments.

Panel Charts are useful, easy to create and simple to understand.

So today, in this tutorial, you will learn a step by step process to quickly create a panel chart. So, let’s dig a bit deeper into it.

Table of Content

1. Quick Intro
2. Benefits
3. Steps To Create
4. Important Points
5. Sample File

Quick Intro

A panel chart is a group of small charts of same size, axis, type of data which are aligned in such a pattern where you can compare them easily.

In simple words, it’s a grid of small charts which is aligned in a single group.

Panel charts were first introduced by Edward Tufte.

According to Tufte:

Small multiple designs, multivariate, and data bountiful, answer directly by visually enforcing comparisons of changes, of the differences among objects, of the scope of alternatives.

For a wide range of problems in data presentation, small multiples are the best design solution.

Benefits of Using a Panel Chart in Excel

1. Comparing data with a panel chart is a whole lot easier.
2. Creating a panel chart in excel is easy. You don’t have to rush into formulas.
3. You can present more charts in a single worksheet.

Steps To Create a Panel Chart In Excel

The easiest way to create a panel chart in excel is to create small charts for all the variables using same axes. And then, align them into a single panel by grouping them. This alignment can give a simple panel of charts where you can compare them easily.

Here are the steps to create your first panel chart.

I am using below data table for this. You can download it from here to follow along.

So let’s get started.

• First of all, create six different charts for all the variables. (Here departments are the variables)
• Now, next you have to make all the charts bit smaller so that you can adjust them into a panel.
• Select all the six charts, go to -> drawing tools -> format.
• From the format option, change the size of all the charts (Here I am using 1.5 X 2.5. You can use as per space you have in your worksheet).

• After that, align all the charts in a neat manner using the sequence you want.

• Now, we have to change the formatting of all the charts (Default chart formatting is nasty). So first of all, format your first chart.

• And now, use these steps to copy formatting from one chart to another.
  • Select your first chart and copy it.
  • Now, select your second chart.
  • Go to home tab -> clipboard -> paste -> paste special -> format -> click OK.
  • Do this for all 6 charts.

• After that, apply the same axis on all the 6 charts.

• Now next we have to group them.

• Again select all of the them, go to format-> arrange -> group -> group.

Congratulations, your panel chart is ready.

Let me tell you something. Grouping all these charts has a benefit. You can move them and change their size jointly.

Important Points To Note Down

• Axes of all the charts should be same so that you can easily compare them.
• The size of the charts should be same.
• The sequence of the charts will matter if you have something pre-defined.
• You can choose your grid as per your need (Vertical, Horizontal, or Square).
• Make sure to use simple and understandable formatting in all the charts.

Sample File

Other Resources

• Super useful post by juice analytics on panel charts.
• Jon Peltier’s post on vertical panel charts.

Conclusion

The reason I love to use panel charts is they are easy to create. And, as a group, it’s easy to manage them (move, copy resize).

A Panel Chart is useful for all type of the data where a comparison is required. If you have categories in your data you can use a panel chart make a comparative analysis of all the categories.

I hope panel chart will help you to present your data in right manner.

And, if you have a unique use for it, please share with me in the comment box. I would like to hear from you.

Written by Puneet Gogia Founder at ExcelChamps. You can find him online, tweeting about excel, on a running track, or sometimes hiking up a mountain. He has just launched his new inventory management template to help you to track stock easily.