Small Chart Multiples Improve Readability
Assume you’ve been given the task to create a chart that compares Actual and Budget Sales Revenue figures of Four Companies – for the past 12 months. You might be tempted to just highlight the data and click on Insert – Line Chart. You’ll end up with this:
For optimum readability, you can create what looks like multiple charts and format these horizontally. Great thing about this chart?
IT’S JUST ONE CHART!
Multiples of Small Charts in ONE Graph
You can also create chart matrices. Just make sure that you use a common axis, size, font, and spacing in all your charts. In this case we would create a chart matrix if we wanted to compare the sales revenue generated by two different products and compare this with other companies. In which case, our graphic would look like this:
Each chart should basically look like the identical twin of the other except that it is based on a different variable so that it shows a different series. Setting up Excel Panel Charts makes it very easy to compare not only each company’s performance on its own and how it did against budget, but also to compare each company with one another because they are right beside each other and they’re using the same axis.
If you click on the plot area you will see that dividers have been added to separate and distinguish each set of data—in this case, each company. It improves the readability of the Chart by adding a visual separator.
When you create Panel charts, it’s important that the setup of the data for the horizontal axis is as simplified as possible to make sure that the axis is not overcrowded.
Featured Course
Black Belt Excel Package
The Order of Your Mini Graphs
Sequencing is also an important factor to consider when you are designing panel charts. If there is a specific sequence in the organization, say for example, there’s a special way that you always show your company’s products or segments, then you probably want to keep this sequence when you design your panel chart. When that’s not the case, then you should consider sequencing your graphs in the panel in ascending or descending order based on the value of your variable.
Data Table Setup to Create Your Panel Chart
To design your multiple graphs as one chart is actually really easy. All you have to do is get your data organized in a certain way before inserting your chart. Once you have that, you are just a few clicks away.
In this example I would like to compare the actual and budget performance of four companies. I would set up my data table to have companies and months in two separate columns and four columns for Actual and Budget. The easier part to understand is the horizontal axis. It should be as long as you want your panel chart to be. That’s why we need to repeat the months in this case.
Now for the vertical axis: The reason we add two sets of actual and budget series is to cause a break in the plotted line. One set of series is always left empty for each company. This creates a break in the plotted line, i.e. the first set of data does not continue over to the next. You will find that if you copy your data into the empty columns, it ends up connecting the line for company A with the one for company B. When you remove that data and leave the columns empty, it breaks the line. It will make more sense if you download the workbook and play around with the data.
The data table looks like this:
Steps to Create the Panel Chart
Highlight your data – make sure to also highlight the empty cells and Insert a Line Chart. In this example you will end up with four series and four different colors—two different colors for Actual and two different ones for Budget.
Set up standard colors for Budget and Actual. You have the option of making the grid-lines lighter and position the Legend at the top. Make sure to remove the duplicate legends.
How to Setup Vertical Dividers Panel Charts
Error bars work best for the dividers. As a first step put markers where you want to add your dividers. To do this, introduce a scatter plot that has a marker exactly where you want the vertical line to be. Once the marker has been set up on the axis, activate the error bars for them.
Create a table of values to control the position of your dividers.
Say for example the first divider is to be after the 12th month, you should set up the values at 12.5, 24.5 and 36.5 as shown below:
Highlight the table containing the values for the dividers. The values are the position of the markers on the x-axis while 0 is being used as the value on the y-axis. Add the data from this table into your chart:
- Right mouse click on the chart
- Click on Select Data > Add
- For the Series Name, write down “Dividers”
- For Series Values, highlight the Y values from the table
To convert this into a scatter plot,
- Go to Layout tab
- In the drop down select “Series Dividers”
- Go to Design tab
- Click on Change Chart Type
- Select XY Scatter plot
Add the X values by,
- Right mouse click on the chart
- Click Select Data
- Select the Dividers series
- Click Edit
- For the Series X values, highlight the X values from the table
Best would be to have a line that goes all the way up to the end of the plot area regardless of the maximum value of the y-axis. To set this up, switch the axis of the divider series to the secondary axis. That way maximum and minimum values can be defined to make sure that the dividers go all the way up.
To switch this to the secondary axis,
- Go to Layout tab
- In the drop-down menu, select Series Dividers
- Select Format Data Series
- In the Series Option, select Secondary Axis
- Click on the secondary axis label on the chart
- Right mouse click and select Format Axis
- For Minimum, select Fixed and set it to 0.0
- For Maximum, select Fixed and set it to 1.0
- Select the Divider Series
- Under the Layout tab, select Error Bars > More Error Bars Options
- In the Vertical Error Bars tab, by default it is set as Both. Since you want the line for the dividers to go from the marker all the way up to the edge of the plot area, select Plus
- For End Style, select No Cap
- For Error Amount, select Fixed Value because you want the divider to go all the way up to the maximum of the Y-axis. Set this to 1.
- You can also change the color of the Dividers by going to the Line Color tab and selecting a lighter color
Since there is no need for the horizontal error bars you can remove them by going to Layout tab and in the drop-down menu, select Series Dividers, X Error Bars. You can also remove the markers or make them invisible, as well as the legend by clicking on it and going to Format > Shape Outline > No Outline.
To format the secondary axis,
- Click on the secondary axis on the plot area
- Right mouse click and select Format Axis
- Remove the tick marks by going to Major tick mark type > None
- Remove the axis labels by selecting None
- You would want to keep the line because it acts like a divider. It’s color can be changed to match the rest by going to Line Color > Solid Line, and selecting the same color as your vertical error lines.
Finish up your panel chart by removing the border and adding a chart title.
Matrix Excel Charts
To expand on this, let us take a look at another example that I have included in the demo workbook which provides a matrix view for the panel chart. In this case I have two different products, product A and product B— and I would like to compare for each company, the performance in terms of product A and product B on their own but also compare each companies performance with one another.
To create this you follow the exact same steps as in the previous example. The only thing you need to do is to create two separate panel charts, one set for product A and another one for product B. You will place them one above the other chart. One difference is that you need to show the horizontal axis only for the chart that’s below—so you can fully remove it from the chart that’s on top. You also just need to activate the legend for the top chart. The result is a graphic that shows a lot of information in a smart, compact way.
Showing these mini charts right beside each other is a very effective way of comparing different variables for different categories. Whenever you have a lot of data and your chart becomes overcrowded, remember to use panel charts.
Download the Workbook HERE.
Subscribe to my YouTube Channel and get free doses of Excel Tips & Tricks.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
