fbpx
Search
Close this search box.

Making Small Multiples in Excel [Charting Technique]

Share

Facebook
Twitter
LinkedIn

My friend Paresh writes excellent commentary on charts on his blog Visual Quest. Last week he gave a home work, asking his readers to recreate the small multiples chart shown below.

Small Multiples (or Panel Charts) are an effective way to show multidimensional data

I found this quite interesting. Small multiples, also called as panel charts, are a powerful way to depict multidimensional data and bring out insights. They are easy to read too.

So, today, let us learn how to create such charts using Excel.

Step 1: Arrange your data

Almost any chart or visualization worth its salt must begin with proper arrangement of data. Since I could not get the data for the unemployment chart, I made up a few numbers for a fictional Confectionery Company. The data is shown below.

Data for our small multiples chart

So, we have the data for years 2007 thru 2010, for the regions – South, West & North and for the product lines – Chocolates & Biscuits

Step 2: Select Products for one region & make an area chart

Inserting an Area Chart in Excel

This is simple. Just select data for chocolates & biscuits for one region and make an area chart. You should have something like this:

Area chart of one region product sales

Step 3: Resize the area chart & format it

Now, we need to make this area chart closer to what we want.

  1. Select the bottom area series and fill it with white color.
  2. Now resize the chart so that we can fit 3 of them in the area you got.

Area chart after reformatting - step1

Step 4: Add same data to the chart

Now, select the same region data, press CTRL+C to copy it. Select the chart and paste it by pressing CTRL+V. See below demo to understand how to do this.

Adding a new series to a chart by copy pasting

We are doing this because we want to have lines with markers on our chart. But the area chart lines cannot show markers. So we are going to add the same data one more time, but this time format it to be shown as a line.

Area chart with duplicate data - after adding

Step 5: Select the new series and format them as line charts

Select each of the new area series and format as line chart with markers.

You should have something like this at the end.

Area chart after reformatting - step2

Step 6: Format the chart

This is where you unleash the creativity. In order to match the look of NYTimes chart, here is what you can do.

  • Set the fill color between lines to something dull.
  • Format 2 lines in distinct colors.
  • Format gridlines & axis lines to something dull.
  • Set axis maximum to 25 (as all charts in small-multiples should have same axis settings)
  • Set axis major unit to 5.

One of the small multiple chart after final formatting

Step 7: Repeat this for other regions

Now, just copy and paste this chart a couple of times. Just adjust the data source so that we have new charts using this technique.

Final small multiples chart after adding and formatting the charts

Note: Learn how you can add descriptive labels to charts.

That is all. You just made a small multiples chart that looks awesome. Congratulations.

Download Small Multiples Example Workbook

Click here to download the example workbook and play with it. You can see the steps for making one of the charts in the workbook as well.

Do you use Small Multiples or Panel Charts?

I really love to use small multiples or panel charts whenever I am analyzing data or presenting results of the same. They offer excellent value per pixel. That said, they take some time to construct. Also, you must tweak axis settings and plot area to get the perfect result. That is why I prefer the in-cell variation of these charts. They are quick to setup and easy to wow (for more on these techniques, see below).

What about you? Do you use Small Multiples or Panel charts? How do you find them? Please share using comments.

Interested to learn more? Read these

As you can guess, small multiples is one of my favorite ways to explore and present data. So we have written quite a few articles explaining this technique. Read these to learn more.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

30 Responses to “Making Small Multiples in Excel [Charting Technique]”

  1. Jon Peltier says:

    Why color the bottom series white? Make it transparent by changing the fill color to No Fill. Then it doesn't obscure features like gridlines.

  2. Jeff Gordon says:

    Thanks, Jon. That's exactly what I was going to suggest!

  3. Andy Pope says:

    You can not use the transparent fill approach unless you use stacked area and calculate new values. Currently both series are in effect the bottom series. Setting the fill to match plot area simply masks the bottom of the chocolcate series, which luckly has larger data values for all charts.

    I would format the horizontal axis to cross on ticks in order to maximise the plot area usage.

  4. Chandoo says:

    @Jon & Jeff.. good suggestion. As Andy pointed out, we need to use stacked charts and alter the data to get this. Here is how it would look.

    Modified small multiples chart with transparent fill

    You can see the example file here: http://img.chandoo.org/d/small-multiples-example2.xlsx

  5. Jon Peltier says:

    Andy -
     
    I hadn't noticed that Chandoo was hiding part of the other series. He wrote "Select the *bottom* area series and fill it with white color", so I just assumed they were already stacked.

  6. paresh says:

    Thank you Chandoo for those nice words.

  7. paresh says:

    Thank you Chandoo for those nice words.

  8. Bailey says:

    Thanks will use this. Also, nice negative halo marketing plan for selling Chocolates by offering alongside Biscuits.

  9. Haroon Rafiq Khan says:

    Impressive.. very creative.. thank you for sharing..

  10. Fred says:

    Cool visual trick! Thanks!

  11. andreas.wpv says:

    The copy and paste does not work. I don't have paste options (right click) but only 'paste'. And when I use that, I get additional lines in the chart, which are different from the others.

  12. Fred says:

    @andreas.wpv
    paste special not just paste. then you will see the window.

    @chandoo
    your highest point on south for '10 should be 16 but it's showing 10. likewsie, all other graphs' 2010 purple line indicating the difference between cho and bisc. Not cho's data. Please kindly comment.

  13. Prem Sivakanthan says:

    Im very concious of what I put on my dashboards, and it really pays to make every pixel count. Often small charts like these are use to see the general trend of things, as opposed to a specific value. Bearing this in mind, I would often omit the vertical axis on 2 out of the 3 charts, and have a very small axis on the chart furtherest to the left.....obviously this would only work if all scales are the same..

  14. Michael D says:

    I recommend structuring the data like as below. Note that there would be a "space" character in the first column for each blank line. This won't work with a filled area chart, but that fill is extraneous information. This is more flexible than the idea above, because you can actually create a single chart with an arbitrary number of panels.

    Choc Bisc
    South 07 10 4
    08 11 5
    09 14 5.7
    10 16 6

    West 07 6 4
    08 10 5
    09 12 5.7
    10 15 6

    North 07 14 6
    08 18 6.7
    09 19 7
    10 21 8

  15. Mr Nero says:

    For those that are struggling with the pasting of the data series. You can just drag and drop the range you want, it will give you the paste special dialog as if you had clicked paste special.

  16. SomeintPhia says:

    Thank you for this description. This technique works also for more then two lines / areas:

    http://2.bp.blogspot.com/-tdxbg6aZ9oc/TsUv8Ofh3_I/AAAAAAAAA9Y/e1Kh9kqfYJo/s1600/area+3.JPG

  17. Andy Pope says:

    @Mr Nero , that feature was deprecated in xl2007. If done in the later versions the cells will pass thru the chart when dropped and result in them being relocated.

  18. ramakanth says:

    hi,

    was trying this on a pivot chart but was not able to do it ...reason being illl have to select the data every time i change the settings on my pivot table. I tried placeing one chart over other and making it transparent ... no good...
    can sone kindly help me with this?

    Regards,
    Ramakanth

  19. andreas.wpv says:

    @Fred - that's what I am saying. I don't have paste options = I don't have special paste. Neither right click nor menue.

  20. Excel Addin says:

    Dammit,

    I tried to change the markers using VBA, but I was getting the old Chart Object does not support this property error.. Anyone else get this to work using code?

    Thanks for the visualisation though Chandoo, it looks great.

  21. Jon Peltier says:

    If you can't get code to work and want anyone to help debug it, you might get more responses if you actually post the code you've tried.

  22. ross says:

    what is it good to have the colour band between the 2 lines?
    Ross

  23. richard stokes says:

    I find the shaded portion of the graph is a great way to show a range of values for a subject. if you treat the two lines as the upper and lower values (or best and worst case for example), the shaded region can describe the range available which works well for estimating future trends as is can represent the uncertainty of the calculations without pinpointing specific values.

    I found this post useful as I've been doing this in excel without knowing what it was called. I'm looking at stacking up various situations against each other with semi transparent shaded areas so the overlap can be observed.

    I think treating the areas as wide line graphs is better visually than using error bars.

  24. Adam says:

    Really love these charts. I've been trying to recreate with my data, but am running into some problems.

    Specifically, what happens if the lines cross over each other? i.e. the difference between the top & bottom reverses?

    For example, I have two series:

    2 4 5
    1 3 7

    In the 3rd column, the top & bottom series flips.

    I'd like to chart the 'middle shading' in two colors: green when the top > bottom, and red when bottom > top.

    Make sense?

    I think it can be accomplished with the transparent stacked charts, but I'm not having much luck. Any suggestions are much appreciated.

    • Chandoo says:

      @Adam... Interesting question. For something like this, you would end up doing lot of extra steps, as we need to calculate the points where one line crosses another (intersections) using some sort of algebra. Then, the area chart has to be broken to handle all these break points. I think that effort is probably worthless (as data keeps changing).

      But I don't know if there is a better & simpler way of doing this. If you come across something let me know.

  25. Quotenjunkie says:

    Why don't you start with a line chart of the two data series and then add a stacked area chart with the same values and just make the bottom area transparent. This worked out fine for me...

  26. satish says:

    One can also get the same output by using Pivot multi chart feature. One more advantage with pivot is the chats are created dynamically depending on no of groups in your data.

  27. franciele says:

    Amazing, i love the site, this is my blog favorite about Excel.

  28. franciele says:

    Amazing, i love the site, this is my blog favorite about Excel

Leave a Reply