The Dumbbell Plot: a How-to Guide
A walk-through on producing these highly useful charts in Microsoft Excel
Excel is an amazing tool in many ways. And for me, it’s still a go-to for complex data visualization. Even in a world where more sophisticated tools like Tableau, R, or Power BI exist, Excel still has much to offer. As an adjunct faculty member teaching IT and business courses it’s always part of my data visualization curriculum, and there are three main reasons why.
1. It’s nearly ubiquitous. From the college campus to a small business to home computers, nearly everyone has access to Excel.
2. It’s remarkably powerful. While the basic chart options it has are certainly plentiful, with a bit of know-how and some patience you can do some truly beautiful and informative visualizations with Excel.
3. It is is very, very flexible. When you build a chart in Excel, you have control over every aspect of it. Color, labels, size, spacing, backgrounds, titles, axes, etc. There’s almost nothing you can’t change or edit.
And this brings me to the dumbbell chart. The dumbbell chart is very useful because it allows you to compare two points in a series that are on the same axis. So, you can compare two points from different times, different currency values, different test scores … really any data that has two points that use the same scale on the axis. And since you can can combine multiple dumbbells in one chart, they can use space very efficiently.
Here’s an example, comparing pre- and post-test scores from four different testing conditions.
I use dumbbell charts frequently in my work, and am often asked how to do them. It’s surprisingly easy, though a bit tedious. So here’s a walk-through of how to do one using MS Excel 2016. The steps are almost identical in older versions of Excel, so even if you don’t have 2016 you can still do this.
Setting up a dumbbell chart starts like many other charts; with summary data in a small table.
1. To build a dumbbell chart, the data has to be set up in a specific way, and added in an unusual way, too. Normally with a chart you select all or most of your data and insert the chart, and Excel knows what to do. With this, it really doesn’t quite “get it” on its own, so you have to guide it.
2. You need three components to make these charts:
- A series name for each “dumbbell” you want to display
- Two data values that you are plotting/comparing
- Coordinates for spacing; this can be X for a vertical chart, or Y for a horizontal one. Which you choose really is a matter of personal preference, but as a rule I use horizontal versions when I have longer data labels; a horizontal chart gives you more room for that than a vertical chart does.
3. For our example, we’re making a vertical chart (it has small series names that fit well with a vertical orientation) that will display this same pre- and post test scores for four different classes in an experiment; a control class, a face to face class, an online only class, and a hybrid class. Figure 1 shows the data table set up we need for this chart.
4. The above table has these components:
- Column A — Series names
- Column B — Pre-test scores
- Column C — post-test scores
- Column D and E — spacing data (labeled X because they are on the X axis for a vertical chart orientation)
5. To get started with the chart, go to your Excel menu and select Insert. Go to the charts menu, and select the basic scatter-plot; it’s the first one in the menu for the scatter-plot set.
This gives you a blank scatter-plot. We are using a scatter-plot because we want to control exactly where our data points are in relation to the others. We can use the Y axis to give us two points: our beginning and end of the dumbbell. Then the X axis, which will let us set a fixed distance between each dumbbell, letting us precisely control the spacing between each pair of points. You don’t have to have this completely pinned down right now; you can always change it later once the chart is built, if you’re not happy with how it looks or feel it’s beneficial to show variations in how the dumbbells are spaced.
6. Select the blank chart. On your menu ribbon, you’ll see that the menu for Chart Tools has now been activated and that it has two tabs; Design and Format. Ensure you have Design selected, and find the option for Select Data.
7. You’ll get a pop up dialogue box called Select Data Source that lets you pick data for your chart. What we need to do is start adding each of the four series in our data, one at a time. You do this by clicking the button called Add.
8. Once you click Add, you’ll get a new pop up dialogue box called Edit Series. In this box you have three options: Series Name, Series X Values, and Series Y Values.
If you click the select button for Series Name, you can then navigate to your data table and select the first series name in our table: Control Class. And, you can also select your values for you X axes and Y axes. Once you do, it should look like this:
Notice that Excel is showing you what actual values are being used in the blue box I’ve added. This is a quality check for you; it lets you verify that you entered the right information. In this case, I’ve put in the data for the Control Class, with Y axes values of 60% and 87%, and X axis values of 2 and 2. The reason you have two X axis values is because you have two points. Both are sitting at the point “2” on the X axis, so they’re in alignment. You should see something like this in your chart:
Note how it’s shaping up. You’ve got your two points with the right Y axis values, and they’re aligned properly on the horizontal axis. This is the basis for the chart. All you have to do is repeat these same steps for each of the series you want to add, one at a time, until all four are in place. When you finish, you’ll have something like this:
In Figure 8, you can see what your chart looks like now that we’ve added all the data points. See how they’re evenly spaced across the X axis with four points between each set? That can be edited at any time by just changing the values in the data table. I find distances of 4 to be optimal, but it’s up to you to decide on your charts. Note that I also removed all the grid lines. They aren’t needed in this chart and only serve to clutter it. This gets us the basic structure of the dumbbell chart. Now we just have to work on the aesthetics to make it look the way we want.
9. First, click on the “dot” at the top on the left; when you do, both dots on that “line” will be selected because they’re part of the same series…that’s why they’re the same color. That’s our set of dots for the control class. I know this, because I set the x axis values for the control class to “2”… which is just where those two dots are on the chart. See how that worked?
10. Right click that dot and select Format Data Series from the menu that appears. We’ll be spending some time here. A lot, actually.
11. In the format data series menu, click the fill and line icon; the one that looks like a bucket.
12. Click the button for “solid line”
13. Change the line width to .75, and change the color to a shade of grey you like.
14. You’ll end up with a chart that now looks like this:
Now here’s a quick shortcut. You have to repeat this step for the other series. The easiest way to do this is to repeat each step along the way for each series that we do; that way you can remain in the same menu under the Format Data Series menu, and not have to change it. So we’ll add all the lines now before we do anything else. That gives you this:
15. Now we have our lines connecting our data. So now, we can work on the “dots” and make them more what we want them to be. To do that, once again click on the top left blue dot to select that data series.
16. Right click the dot and select Format Data Series again. Click the bucket icon again. Then, the option for Marker, and click Marker Options. Check the button for Built-In, and change the size to 20. You can also change the marker shape here if you desire; Excel has several options, including the ability to upload a custom shape!
What you get is this:
17. Now, while you still have that menu open, go ahead and take the same steps for the other three series. You’ll end up with this:
18: Now we need to add some data labels to our markers. To do that, once again right click the top left blue marker again. This time, select Add Data Labels, the Add Data Labels again. Figure 15 shows the menus for this.
19. This will add your labels, but it adds them to the default position at the right of the data markers.
Go ahead and add all your data labels to your other series. You’ll end up with this:
20. Now we need to re-position our data labels. To do that, just click one label in any series; that will automatically select both labels for that series. Then right click it, and change the position of the labels to Center.
Repeat this for your other series, and you’ll have this:
21. The next thing to do is eliminate your X and Y axes. You don’t need them; the X axis is just there for spacing so the numbers don’t mean anything. The Y axis is redundant, because you have data labels. So just click each one and hit “delete” on your keyboard. That will clear them out.
You can also click your data labels and change them if you like; bold them, change the color, etc. It’s up to you what you want to do. I prefer to make them bold regardless of color.
22. From here, you have some personal decisions to make. The biggest one is how you plan to use color here. Color tells your users a lot about what you’re trying to say. If the main point of this graphic is to compare the pre-and post scores, then using the same color for all pre-scores and all post scores will visually make that point for you. That’s how I did it in my example. However, your main point may be that you want to emphasize different groups and you’re not concerned as much about the actual scores. If that’s the case, you might use different colors for each group, or to highlight one group over the others. In the example below, I put the focus on the control class and de-emphasized the others by making them all grey.
To change the colors, right click on any series and once again go to Format Data Series. Then click Marker and Marker Options. You’ll see menus for Fill and Line. Fill lets you change the color of your markers, and line lets you change the border color.
You can even change markers in a series to individual colors, shapes or sizes by clicking on one mark in a series, then clicking it again so that you only select that one.
There are a lot of ways to format your markers, but keep in mind what the point of your graph is. Use marker color, spacing and shape/size to emphasize your points and tell your story. You’d be surprised at what just changing color and spacing can do! In the example below, I used space and color to differentiate between the face to face and virtual class “types” in order to make the emphasis that I’m comparing those two modes of instruction.
23: The next thing to do is add some series labels. Unfortunately there’s no easy way to do this using the chart tools themselves. But, you can easily add some by using your Insert Tool on the menu ribbon, and insert a Text Box.
Type in your label, and move it to where you want it on the chart. Format it to whatever look you want it to have; the font size and color, the border, fill, etc. Once you have that done, copy it, paste it, and change the label for another series. Just repeat that until you have a label for each of your series in the chart. Try to keep your labels aligned with each other and with your series!
24. All you need to do now is add a title! Just remember that your title should tell your user what the chart is trying to say … not just what it displays. Titles can go a long way to helping your user interpret the chart the way you intend and help make a decision.
And that’s it! Like I said, it’s not hard; just a bit tedious. But it’s well worth the time to learn this, and the nice thing is once you get your first one built out, you have a template you can re-purpose for other data. That makes it a lot faster to do it again.
I hope this is helpful! Happy charting!
Mike Raper works as an Evaluation Faculty member at the Center for Creative Leadership. In his work he collects and analyses survey and assessment data and prepares presentations, reports and dashboards for internal and external clients. He is also an adjunct faculty member at Guilford College where he teaches courses on Information Design, Research Methods and Management Information Systems. He is certified in both Tableau and Qualtrics and specializes in dashboard design for survey data.
