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
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.
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.
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.
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.
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 trick to make sure each chart’s plot area is the same width and height.
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.
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!
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.
Bonus
Click below to download my Excel file.
Download the Excel File
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 […]