1 Comment

  • […] two strategies: Either a) create two separate charts, a strategy demonstrated in previous posts like this one, or b) use floating bars, a strategy demonstrated in previous posts like this one. Stephanie […]

  • Leave a Reply

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

    How to Make a Small Multiples Bar Chart in Excel

    Updated on: Jul 27th, 2013
    Data Visualization in Excel
    , , ,
    Screenshot showing one of the steps of how to create small multiples bar charts in Microsoft Excel.

    Two weeks ago, I challenged readers to re-create the “after” version of a small multiples bar chart. You can read the full post here.

    Congratulations to the 6 contestants! Click on the contestant’s name to see their chart.

    Most of them even applied this chart type to their own datasets. Sara, Elisa, and Angie ended up using different types of bar charts altogether. Check ’em out!

    Now it’s time to post the how-to guide.

    Step 1: Study the chart that you’re trying to reproduce in Excel.

    We’re trying to re-create a small multiples bar chart like the one shown below. We’re comparing how many small, medium, and large nonprofits reported using each evaluation technique.

    Small multiples bar chart

    Small multiples bar chart

    Step 2: And the secret to making a small multiples bar chart in Excel…

    …is that we’re going to make six separate clustered bar charts. When we copy and paste the charts from Excel into PowerPoint or Word, they’ll look like a single cohesive chart.

    Six small charts come together to look like one big chart.

    Six small charts come together to look like one big chart.

    Step 3: Type the data into Excel.

    Here’s one of several ways to align your data.

    Screenshot of a Microsoft Excel spreadsheet.

    Step 4: Create the first bar chart.

    We’re going to create six bar charts. I started with Internal Tracking Forms.

    Screenshot of a Microsoft Excel spreadsheet showing a bar chart.

    A default bar chart showing the percentage of small, medium, and large nonprofits that use internal tracking forms.

    You know the drill: Add data labels inside the end of your bars. Delete the legend, title, tick marks, grid lines, and horizontal axis label. (Later, we’ll insert new text boxes to label everything.) Adjust the axis so it goes from 0 to 100% (rather than 0 to 70%). Change the bar color. Use gray text to de-emphasize less important information like the axis labels. Reduce the gap width from 150% to something closer to 30% or 50%.

    Hot tip: Keep the borders. We’ll delete the borders at the very end. The borders help us keep all the charts and text boxes aligned and even.

    After a few clicks, we've improved the general look and feel of our bar chart.

    After a few clicks, we’ve improved the general look and feel of our bar chart.

    Beginner Excel users: If you need extra instruction, check out how to make a basic bar chart and my Excel for Evaluation chart tutorials.

    Step 5: Copy the first chart.

    Rather than re-create the wheel when making the second, third, fourth, fifth, and sixth bar charts, let’s save some time by simply copying the first chart.

    Just use good ol' fashioned copying and pasting to create a second bar chart.

    Just use good ol’ fashioned copying and pasting to create a second bar chart.

    Step 6: Populate the second chart with the second chart’s data.

    The first chart is for Internal Tracking Forms and the second chart can be for Interviews. Use the “select data” feature to put the Interview percentages into the chart.

    Screenshot of a Microsoft Excel spreadsheet showing bar charts that are blue, green and red.

    To reduce cluttering, delete the second chart’s axis labels and use the business card trick to make sure each chart’s plot area is the same width and height.Screenshot of a Microsoft Excel spreadsheet showing two bar charts that are blue, green and red.

    Step 7: Make the third, fourth, fifth, and six bar charts.

    Do some more copying and pasting to create the third, fourth, fifth, and sixth bar charts.

    Do some more copying and pasting to create the third, fourth, fifth, and sixth bar charts.

    Step 8: Add text boxes to label everything and delete the borders.

    Insert text boxes. Once everything is aligned, delete the borders.

    Save time by copying and pasting text boxes, too. No need to create every single one from scratch!

    Save time by copying and pasting text boxes, too. No need to create every single one from scratch!

    Step 10: Paste the charts into PowerPoint or Word.

    Since we’ve got 6 charts and 14 text boxes, copying and pasting into PowerPoint or Word can be a pain.

    Hot tip: Carefully select all 6 charts and 14 text boxes. Right-click and “group” all the items together. Then, you can copy and paste into PowerPoint or Word with a single click!

    Grouping the items together makes inserting your small multiples bar chart into Word or PowerPoint a breeze.

    Grouping the items together makes inserting your small multiples bar chart into Word or PowerPoint a breeze.

    Bonus

    Click below to download my Excel file.

    Download the Excel File
    More about Ann K. Emery
    Ann K. Emery is a sought-after speaker who is determined to get your data out of spreadsheets and into stakeholders’ hands. Each year, she leads more than 100 workshops, webinars, and keynotes for thousands of people around the globe. Her design consultancy also overhauls graphs, publications, and slideshows with the goal of making technical information easier to understand for non-technical audiences.

    1 Comment

  • […] two strategies: Either a) create two separate charts, a strategy demonstrated in previous posts like this one, or b) use floating bars, a strategy demonstrated in previous posts like this one. Stephanie […]

  • Leave a Reply

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

    You Might Like

    Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.

    Enroll

    How to Bring Your Technical Tables to Life

    Just because I’m pro-graph, I’m not anti-table. Technical tables have so much value, especially as visual appendices for reports. In this blog post, you’ll get ideas for bringing your technical tables to life.

    More »

    Need practical how-to tips? In this course, you’ll learn how to make great graphs inside software you already have. Includes beginner, intermediate, and advanced tutorials for making charts from start to finish.

    Enroll

    Subscribe

    Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.