• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Small Multiple Charts

You are here: Home / Excel Charts / Excel Small Multiple Charts
Excel Small Multiple Charts
November 19, 2019 by Mynda Treacy

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.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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

Excel Small Multiple Charts

More Excel Charts Posts

picture fill excel charts

Picture Fill Excel Charts

Using a stylish picture fill in your Excel Charts is a simple way to make your data visualizations more captivating and memorable
excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.


Category: Excel Charts
Previous Post:Overlay Line Chart LabelsOverlay Line Chart Labels
Next Post:Extract Start and End Dates with Power Queryextract start and end dates with power query

Reader Interactions

Comments

  1. Microsoft Excel Recalc or Die

    December 6, 2019 at 3:12 pm

    Doubel tap on Jim´s post: “less is definitely more”.
    The Gap trick is amazing!

    Reply
  2. jim

    November 22, 2019 at 6:06 am

    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
    • Mynda Treacy

      November 22, 2019 at 9:21 am

      Glad you’ll be able to use it, Jim 🙂

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.