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!)

Capture

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.

how to create panel chart in excel if a normal chart is not able to show comparison

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.

a panel chart in excel showing average salary of 6 different 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.

data-table to create a panel chart for average salary for 6 different departments

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).

select your all the charts and go to format options to change their height and width to create a panel chart in excel

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

place all the chart in a manner to create a Panel chart in excel

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

add formatting from one chart to another to create a panel chart in excel

  • 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.

change axis for all the charts to create a panel chart in excel

  • Now next we have to group them.

 

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

click on group option to group all the 6 chart

Congratulations, your panel chart is ready.

with group option you can adjust your panel chart easily

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

download sample file to learn about panel chart in excel

Other Resources

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.
Subscribe to my mailing list.

Leave a Reply

%d bloggers like this: