(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
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.
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
- Comparing data with a panel chart is a whole lot easier.
- Creating a panel chart in excel is easy. You don’t have to rush into formulas.
- 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.
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.