Excel Small Multiple Charts

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:

Instead of busy charts like this:

Download Workbook

Watch the Video - Small Multiple Charts

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:

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:

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:

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

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:

Now that your ranges are aligned, scroll down the list of Horizontal (Category) Axis Labels and 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:

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

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!