Small Multiples in Excel

Introduction

In January of this year, I wrote a page about Small Multiples using Power BI. Small Multiples are what we used to call Panel Charts but, good as they could be, they could take a lot of effort to create. Small Multiples is essentially a one click solution to the creation of Panel Charts.

You can read that page here: Small Multiples in Power BI 

In this page, I am introducing you to Small Multiples in Excel and at the end of page I share with you what happened when I shared my small multiples idea in excel with someone!

Panel Charts

A reminder of what a panel chart looks like:

It's a cheat in that whilst there appears to be just one chart, one panel, they are actually four separate charts made to look as if it is just one.

Power BI Small Multiples

Another reminder, small multiiples in Power BI this time:

Small Multiples in Excel

So, now you know via these reminders and/or via my previous article on the topic, what small multiple charts might look like. Well, if you don't want to spend time creating panel charts and you don't use Power BI but you are an Excel user, here are three screenshots to whet your appetite:

The raw data: I kept it small and simple:

Unpivot the Data in Power Query:

Create your small multiples:

As far as I know, I am the only person who is creating and using small multiples in Excel, except for the delegates to some of my online courses, where I have shared my technique. Having said that, it wouldn't take an experienced Excel user too long to work out how to create this kind of graph.

Still, you can write to me and ask for my small multiples spreadsheet, attend one of my courses or work it out for yourself!

You're Cheating

If ever you communicate with any Excel MVPs or very well known Excel people, the responses you get can range from brilliant to downright disheartening. Let me praise the likes of Bill Jelen, Matt Allington (although he's more Power BI these days, Chandoo and others. I won't name any downsiders because they are not here to defend themselves.

Anyway, earlier this year a well known Exceller posted a note to say he was hosting an Ask Me Anything webinar ... please send your questions. I sent him two questions: he couldn't answer the first question any more than I could. Then I asked about small multiples in Excel and he had a stab at them. He was wrong but he tried. I was interested, therefore, in his response to my small multiples solution. I sent him a working file with explanations. A true downsider, he clearly didn't know them and he dismissed my idea as cheating. I know his work, I used some of his ideas in the past. I will have nothing to do with him again, though.

If someone engages with you, especially if you've asked them to, at least have the good grace to be civil and if you don't know something, be adult enough to own up to it. Also give credit where it's due.

Duncan Williamson

3rd September 2021

3rd August 2022