How to Make Horizontal Dumbbell Dot Plots in Excel

In case it wasn’t clear, I freakin love dot plots. They are amazingly easy to read, beautifully simple in their display. I was making these babies for some clients a little while ago, before and after dots for about 25 variables in one graph. And they said “Uh, hey yeah Stephanie? Could you, like, draw a tiny line between the pair of dots on each line?” >.< That was my face when I imagined painfully inserting 25 lines, perfectly aligned between the dot pairs. But I love challenges like this. Could I find a way to make Excel do this for me?

Hell yes I could.

Read below for my old instructions. I vastly simplified this process, though, and posted updated instructions here. Use this extra time you now have to go back the world a better place, eh?

Ultimately, it looked like this:

A regular dot plot is made with a basic scatterplot as its backbone. To make the dots connected, like tiny dumbbells, the backbone is just a connected scatterplot. But the construction is a little bit different. My data table is as so:

dumbbelldotplotdatatableI need a set of y values to accompany each of my x values (the stuff I really care about displaying). Note that I ordered the post scores from least to greatest. Then for the pre and post y values (columns D and E) I typed in values that were .5 apart from one another. This will force each pair of dots to appear on its own line.

So insert a connected scatterplot without selecting any data and then right-click on the empty space and click Select Data. It’ll open up this window:

dumbbelldotplotdataseriesEach pair of dots will need to become its own series. So you’ll click on Add to make a new series.

dumbbelldotplotaddseries

The little window you see above will open up. For Series name, click on the name of the group (Group B). For Series X values, select Group B’s pre and post scores. For Series Y values, select Group B’s pre y values and post y values (Columns D and E). Click OK and then repeat this for each group until they are all displayed on your graph.

It’ll look funky at first.

dumbbelldotplotfunkyYou’ll have to go in and carefully change each marker to a circle. Right-click on the markers and click Format Data Series. Up in here, select the Built-in option and choose the one that looks like a sunburst (it’s really a circle) and increase the size to 20:

dumbbelldotplotmarker

Change the color of each marker to correspond to your pretest color and your posttest color. Add the labels in the center of each dot. Finally, change the line color away from the Excel defaults. Right-click on either marker and select Format Data Point. In this window:

dumbbelldotplotlineSelect Line Style from the choices on the left and adjust the width of the line (I ended up using 2 pt). Select Line Color and pick gray or black or something unassuming.

I thought we were good to go but my clients said they’d prefer if we brought better attention to the places where the scores actually decreased. While it would appear that one could simply change the line style in the window shown above such that it began or ended with an arrowhead, in actuality the arrowhead is obscured by this awesome size 20 dot. So I manually inserted a tiny triangle, which wasn’t too painful, especially since we only applied it to a small portion of the dumbbell dot plot pairs.

Add in a sweet title and some textboxes with labels and now we are talking about one heavy-lifting data visualization. Check out a recent report by OHA that uses dumbbell dot plots (I consulted on the design).

AND FOLKS! This is a great example of choosing a graph type that is appropriate to the data. Dot plots are awesome for showing comparisons between two (or sometimes more) points. This would score a 2 on the Data Visualization Checklist item “The type of graph is appropriate for data.” 

This is a very early draft of a section now in my latest book, Effective Data Visualization. It has loads of advice on the best chart type to use and how to make it in Excel. Video help is available at the Academy and in our Graph Guides program.


Learn in the Academy!

You can find step-by-step instructions on how to make 60+ awesome visuals in my Evergreen Data Visualization Academy.

Video tutorials, worksheets, templates, fun, and a big-hearted super-supportive community. Learn Excel, Tableau, R or all three. Come join us.

Enrollment opens to a limited number of students only twice a year. Our next enrollment window opens April 1. Get on the wait list for access a week earlier than everyone else!

Master Dataviz with Graph Guides!

Our newest program, Graph Guides, is a custom-built, year-long sprint through 50 Academy tutorials.

When you enroll, we’ll assess your current data viz skill set, build you a customized learning path, and hold your hand as you blaze your way to new talents.

We open enrollment to 12 students at a time and only twice a year. Get on the waitlist for early access to our next enrollment window.

17 Comments

  1. LOVED your workshop and have since been working on a report – using the dot lines to convey multiple pieces of info all in one place. Still a work on progress but so pleased with the reduction of info that I have to talk about in the text – better still, makes me talk about the big picture – not piece by piece. Yeah.

  2. I put this into action today – created dumbbell dot plots to show increased levels of confidence over time for program participants. SO beautiful! It’s a really good thing you’re blogging about this stuff, because I never could have figured it out without your step-by-step guide! <3

  3. Hi, I’m having trouble with getting the vertical axis label to display the name of the groups on my chart. Can you help? I’m so close, yet so far…

  4. Thanks for this “How To”. I just pulled one together on some of my project data and it looks gorgeous.
    One tip I would add to this is post is to do all the data series and data point formatting before you format the labels. For newbies this will speed things along (it will save them from lots of missed clicks on tiny objects), especially if they working with a healthy number of groups. I am tempted to look into creating a macro to speed these changes along, but now that I have a template it seems easier to drop my data in and tweak from there.
    Putting your training at Westat to use. Thanks!

  5. Hi Stephanie! Excellent tutorial, this is coming in handy for a website satisfaction score comparison by visitor segment.

    I do have a question though: what about accounting for sample size in each group? Were your groups similarly sized where this wasn’t a concern? For my data, some of the groups with the largest spreads had just a few responses, so I want to account for that when communicating the data. Do you have any thoughts on this?

    Thanks!

    1. Stephanie Evergreen

      Hey Lea! I suppose you could add the sample size to the category label if it was important to communicate. Sounds like in your case the difference was so drastic it would warrant it. But readers may not always understand that differences in sample size should affect how the graph is interpreted so if it’s a situation where a reader would make serious misinterpretations, don’t graph it at all.

  6. When you select data series (i.e., group B in your example) how do you not have a preset icon next to the label in the legend. I can move the legend to the left side, but there are icons next to it. Also, when I initially made my graph like the directions, the circles from each “y axis” were overlapping into the next circle on the .5 line lower or above. How do you fix these things. Loved the tutorial and just hoping for a few clean-up tools. Thanks!

    1. Stephanie Evergreen

      Hey Melissa, I’m not totally sure what you mean, but I’ll try. I don’t have any legend in here at all. I’ve used textboxes. You can delete the legend in your graph if one appears. If your circles are overlapping, try making the graph bigger or the circles smaller. Does this help?

  7. Hello Stephanie, excellent job ! thanks ! i just sent you by mail a solution to add category names (new series, a bit long to do but it works …) and i have not been able to explain why/how lines in the graph are not ordered like in the data table ? i saw that you have ordered the data but it has no effect on my graph …

  8. Hi Stephanie,
    Wanted to share 2 slight variations in how I’ve made dot plots in the past. One, I use a different color whether the post data are higher or lower than the pre. And two, match the connecting line to that color so it looks like a tail or trail to follow. I think these 2 changes make it easier to spot increases or decreases, as well as by how much. Especially when you’re looking at more rows, like test scores for 30 kids in a classroom. Example

  9. Stephanie, I want to thank you for sharing this tool tip. I always loved dumbbell charts but I never tried to make one in Excel as I am mainly a PivotChart guy. Great job and I can’t wait to check out your book.

  10. Hello Stephanie! Thank you for sharing your knowledge! Would it be easier that instead of inserting the triangle mannually we simply duplicate the two dots between which we want to insert the arrow, that we change this new dots into a line and choose arrow on the left end. After that we try to change the duplicated data by increasing the left dot and decreasing the right dot until we get an arrow. Probably that will be helpful easpecially if we try to change the diagram size, because the manual triangle may move and we will have to put it back again. Happy day!

    1. That could work, so long as you put the new line and arrow on top of the old dots. Otherwise, the dots are too big and they hide the arrow.

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.