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.
- James Coyle
- Sara Vaca
- Elisa Avila
- Angie Ficek
- Amanda Drescher
- Bryan Stephany
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.
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.
Step 3: Type the data into Excel.
Here’s one of several ways to align your data.
Step 4: Create the first bar chart.
We’re going to create six bar charts. I started with 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.
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.
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.
To reduce cluttering, delete the second chart’s axis labels and use the business card trickto make sure each chart’s plot area is the same width and height.
Step 7: Make the third, fourth, fifth, and six bar charts.
Step 8: Add text boxes to label everything and delete the borders.
Insert text boxes. Once everything is aligned, delete the borders.
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!
Click here to download my Excel file.