Excel Small Multiple Charts

Mynda Treacy

November 19, 2019

Reduce clutter in busy line charts by plotting your data in small multiple charts, or panel charts as they’re sometimes known. I’ve used a few tricks for building small multiples in the past, but the Excel small multiple charts I’m going to cover in this tutorial are dead easy. It’s all in the layout of your source data.

Small multiples allow you to create clear and easy to read charts like this:

Excel small multiple Charts

Instead of busy charts like this:

busy charts

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Watch the Video - Small Multiple Charts

Subscribe YouTube

 

Tricks to Building Small Multiples/Panel Charts

We’re aiming for a horizontal axis that has a space either side of the start and end of year labels:

building small multiples

This space prevents the chart plotting one continuous line and it gives each region a more defined area without the need for vertical lines separating them.

We achieve this by laying out the chart source data with a blank row above and below each series. You can see these spare cells highlighted in pink in the image below:

blank row above and below each series

Also notice that the Region labels are in the row containing the blank Year and Value cells. This ensures they are centered in the axis.

The ‘Gap’ on row 32 prevents the horizontal axis including a tick mark in the last region. The Gap cells are in the chart series range, but as you can see in the image below, it is deselected in the Horizontal category axis labels:

gap cells

Building Small Multiples/Panel Charts

Step 1: Select the Data and Insert Line Chart

When inserting the chart initially you only want to select the Year and Value cells; B4:C32 then Insert tab > 2-D Line Chart.

Step 2: Add Region Field to Horizontal Axis Range

Right-click the chart > Select Data. In the dialog box Edit the Horizontal (Category) Axis Labels

edit the horizontal category axis labels

While in the Select Data Source dialog box, edit the Legend Entries series to include cells C4:C32 and give the series a name, or link it to cell C3:

edit the legend entries series

Now that your ranges are aligned, scroll down the list of Horizontal (Category) Axis Labels and deselect ‘Gap’:

deselect gap

Job done!

With this method you can add more regions/categories to your panel charts as required.

The benefit of building small multiples or panel charts using this technique is that the vertical axis is automatically the same for all series which is essential for comparing the regions on a common scale.

Panel Chart Variations

You can also create different chart types like this actual vs target using an area chart and line combo:

small multiples charts with different chart types

Or repeat the series in each panel, highlighting one category at a time:

highlight one category in small multiples charts

This allows the reader to see each category in the context of the others.

Small Multiple Chart Shortcuts

If you want an easy way to build small multiple or panel charts then check out Jon’s Chart Utility that builds small multiple charts and more with the click of a button. It’s available for PC and Mac.

Related Topics

Line Charts vs Dot Plots - for when the number of lines gets crazy!

line charts vs dot plots

3 thoughts on “Excel Small Multiple Charts”

  1. Hi Mynda,

    thanks for the GAP tip – that’s been annoying me for so long; thought I’d tried everything but that’s so simple and effective

    as always with charts, less is definitely more

    jim

    Reply

Leave a Comment

Current ye@r *