Blog

Easy Dot Plots in Excel

Easy Dot Plots in Excel

Last month I was at a Naomi Robbins’ workshop and she was pretty emphatic that dot plots are the better method of visualization, as compared to bar charts. The reason goes back to Cleveland’s early experiments on visual perception, which found that humans most accurately interpret locations on line, when those lines share a common axis. Even more accurate than length, which would be a bar chart.

The problem is that most of us use Excel and dot plots are not a default chart option. On Naomi’s inspiration, I used a little elbow grease to make it work. Here are the instructions. And I think you’ll agree that the dot plot really does allow for better comparison between two points over a side by side bar.

DotPlotTable

My data table looks like this. Fall and Spring columns hold my actual values (kindergarten readiness scores). The DOT SPACING values are just my placeholders. They’ll put the Fall and Spring values on evenly spaced lines.

First I select “Fall” and it’s values and “DOT SPACING” and it’s values and then insert a scatterplot.

 

Doing so produces something like this:

dotplot1

Then I right-click on the graph, choose Select Data. I edit the name of this series so it points to the cell with Fall.
Then I add a new series and select the Spring data for the x axis and again select DOT SPACING data for the y axis. That produces this graph:

dotplot2Next delete the legend.

And format each data series (right-click and select Format Data Series) so that the marker size is larger, more like 20. I change the reference point (Fall) to a gray color and I change the most recent point to my action color (red).

Then add data labels (right-click and select Add Data Labels). Right-click again and select Format Data Labels. Change the settings there so the labels display the x values, are centered over the data point, are white, bold, size 14 or so.  Those adjustments bring us to this chart:

dotplot3Finally, adjust the y axis so the top line is gone. Just change the maximum number on the axis to the highest number you have listed in the DOT SPACING values. Then delete that axis.
Also adjust the x axis as needed (mine needs to stop at 100).
Then add a title and subtitle.
Labels are trickier here, so I just inserted textboxes right over the chart. Here’s my final product:

dotplot5So the key is to set up a hidden y axis of spacer values that serve only to put your data points on lines that are evenly spaced apart from one another. Other people also have methods for forcing a dot plot out of Excel – this one is really easy and totally free. Give it a try. It really does support comparisons pretty well, and would even if the number values weren’t displayed. Good luck!

This post is an excerpt from 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.

32 thoughts on “Easy Dot Plots in Excel
  1. Jon says:

    Nice step-by-stop of how to easily make a dot plot in Excel. Just a few things I would add:

    -You can use a combination of the ROW and ROWS formulas to construct the data in the “DOT SPACING” column instead of doing it by hand.
    -You can use horizontal error bars to create custom lines between the points or from the points to the axes. For example, you could delete the gridlines and use a horizontal line to connect the pairs of points.
    -You could add additional series that sit just to the left of your left-most data point to use as labels instead of text boxes. For example, insert a new scatterplot point with the series name “Literacy” for which the x-value=25 and the y-value=2.5. It’s a little time consuming, so a macro would make it easier.

    Thanks.

    • Karthik says:

      Hi Jon,
      Can you elaborate on the last point you have made regarding data labels? I added another scatter plot with Dot spacing & items (Literacy, Language, ..) as my axes – but how do I merge these two charts so that items appear as data labels on the main chart? Thanks!

    • Julie says:

      Hey there. My sister’s boss is requesting something odd for her to change on her scatter graph. Maybe you can help. Here’s her sitch…
      We have a scatter chart that has percentages all along it from dates between Feb, 2013 and Oct, 2014. My boss would like me to change the intervals between the plotted ‘dots’ on the graph so that the dots for all the figures in 1993 are in the first inch of the graph, the dots for all the figures in 1994 are in the second inch of the graph, etc. Since some years only have 1 or 2 dots and other years have 4-6 dots

      • Stephanie Evergreen says:

        Sounds like you’ll need to set up a table with a column for each month (or whatever consistent reporting period you want) and you’ll only have data in some of those columns. But, right, that’s a different discussion than this dot plot. Good luck!

  2. Shell says:

    This tutorial is a god send! I’ve never made a scatter plot in Excel and have been struggling for a few days now trying to create one with my data. Your tutorial is awesome because it’s so simple and it MAKES SENSE TO ME. Sometimes you read tutorials and wonder how to replicate them with your own data. Thank you so much. This tutorial was incredibly helpful.

  3. Vince says:

    Another option for the labels is the free XY Chart Labeler add-in for Excel: http://www.appspro.com/Utilities/ChartLabeler.htm

  4. Susan Kistler says:

    Love this example and am using it for a class on data visualization. I tried out the XY Chart Labeler recommended by Vince and it worked wonderfully on my pc. Since each point can have only one label, you simply add another column titled ‘labels’ and I set each value at the Fall value minus 2 (thus 32 for literacy) and then added the series, removed the marker for that series, and used the plug-in to add the labels based on this additional column (then formatted the labels – made font larger, selected color, added white fill to label box). The advantage was that the labels then moved automatically with the points on the graph if I had to resize the whole graph.

    Thanks so much for walking us through this useful and attractive graphing option.

    • Stephanie Evergreen says:

      How did it work if your labels are long? I haven’t used it in a while but when I did I found it still wrapped and centered long labels, as Excel awkwardly does.

      • Susan Kistler says:

        Worked well – they didn’t wrap. But none of the labels were that long.

        I just checked on the version I made and I can also select any individual one and resize the box if needed (so if “Creative Arts” for instance had wrapped, I could select just that one and then make it bigger.

        One of my current challenges is working across multiple versions of excel. I have a macbook air that I use by default, a 17″ macbook emulating a PC that I use for presentations (big so I can see!), and then a PC desktop. I just signed up for office 365 in hopes of getting them all running the same (or similar given mac/pc) current versions of software (noticed that one of the three was back on excel 2003). The new versions seem to be treating labels much more nicely.

        • Jason says:

          Thank you, Stephanie and all contributing in the comments. This made such a difficult task so easy. However, after I have installed the free XY Chart Labeler, and maybe am just having trouble mastering it, but as I go to label my “Fall” data series, I lose my numerical label of the X axis. It strikes me that this might be an issue. Is a single data series allowed to have multiple labels? If not, I’m not sure you can label values on your first data series, and also label it with text using this tool. Does anyone have any ideas?

          • Stephanie Evergreen says:

            I don’t use the chart labeler but native Excel does not let you add two labels in different places. A single series can have multiple labels, just not in different places. If the chart labeler is causing the same issue, just insert a text box or go for a fancier option (details in my next book).

  5. Pierre says:

    Naomi Robbins in her excellent book “Creative More Effective Graphs” mentions a little macro for the labeling of the y axis. It can be freely downloaded at ftp//ftp.wiley.com/public/sci_tech_med/graphs/.
    It works a treat.

  6. Kris says:

    This was a great tutorial and I’d love to incorporate these graphs into my work. One questions though: what do you do when you have two overlapping values, e.g. 98/99 or 99/99? My workarounds are ugly.

    • Stephanie Evergreen says:

      I drag one of the numbers slightly outside the circle. Or delete it (it should be obvious that they are super close values by the overlap, right?). OR consider that the dot plot might not be the best chart type for your data.

  7. Logit says:

    Tons of thanks for this very clear explanation. Just what I was looking for.

  8. Lyssa says:

    Thank you for this tutorial! I really appreciate the ability dot plots have to portray deeper information that bar graphs might miss (e.g., variance). I am currently trying to create a dot plot for original data (opposed to a group average). I’m trying to follow the steps of this tutorial and add each response set as a series, however, with a large number of responses (~200) this process might take me forever! Do you have any suggestions for short cuts that could speed up my process? Thank you!

    • Stephanie Evergreen says:

      Try recording a macro! It’s more complicated than I can explain in a short reply, but google it and you’ll see ways to record your steps. You’ll want to give yourself lots of time to experiment. And you may find you need two dot spacing columns for the macro to work.

  9. Regi Hampton says:

    I love dot plots but what is the best way to sort them? Descending or ascending, on the grey or the brown? Your sorting can effect how your story is communicated.

  10. Marco Torchiano says:

    Thanks for the quick and clear tutorial. I eventually managed to get an acceptable (albeit rotated) dot plot in excel using a “Line” chart. More details: http://wp.me/pmzS2-eF

    • Stephanie Evergreen says:

      I’d call those Lollipops and I’ll publish a shortcut soon that requires no rotation. That said, I LOVE your curiosity and exploration. Thanks for sharing it with us!

  11. Miranda Lee says:

    I used the information from this and the other tutorial on how to build dumbbell plots as part of my AEA presentation, and cited you frequently! Thank you so much!

  12. MG Verde says:

    I am looking to remove the y axis labels and lines together, but make the scale very small such that the blue diamonds stack on top of each other. So, in your test score data if we have 7 100s at that point on the x-axis Excel show 7 blue diamonds tip to tip towards the sky. Any thoughts? I have played with the axis scale but can never get it.

    • Stephanie Evergreen says:

      Try quarter intervals or smaller? You can always adjust the minor units to get more grid lines to show up.

  13. Michael Pierce says:

    Great tutorial that was nice and easy to follow. My only suggestion would be to eliminate the X axis along the bottom. It really isn’t adding anything to the chart. Or, if you feel that it’s necessary, maybe only show the max/min values (0% & 100%) so people understand you’re talking about percentages. The interim values really don’t add anything, in my opinion.

    • Stephanie Evergreen says:

      I agree! In this case I sometimes leave the axis in because it’s a pretty new graph type for people and I want them to know its a real graph, not just circles inserted in Illustrator. But you certainly don’t have to leave all the increments in there!

  14. Marysia says:

    Hey there! Thanks for the great tutorial! I’m just wondering if there is a way to make the dots’ sizes proportional to their values? My coworker and I are trying to (somewhat) recreate the visuals on page 7 of the State of Evaluation Report- 2012 – (http:/stateofevaluation.org/) which I’m not sure are even technically dot plots. But we really like the visual of the dots’ sizes varying according to the value.
    I’d appreciate any advice/ clarification you might be able to provide! Thanks!

    • Stephanie Evergreen says:

      That would be a bubble chart, which is a default graph type in newer versions of Excel. But be very careful. Most of the time the size of the bubble creates more confusion than clarity.

  15. minerva says:

    game changer. Used the dot plot for one series then added new series which resulted in a horizontal bar chart indicating total chronological volume and the scatterplot represented the average points in time for events.. 2 x-axes with exactly similar scales, 1 y-axis with the “dot spacing” which I removed via axis editing for “no label”. Exactly what I needed but which excel was giving SO MUCH difficulty. There was tons of elbow grease involved in changing chart type and moving series to alternate axes but it’s been done and it’s beautiful. Almost 2 weeks worth of struggling was solved in 1 day’s worth of work. Thank you.

  16. Oscar says:

    Pure gold. Making Excel exciting again!

Leave a Reply

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

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

RT @Evaluate4change: New Blog: Evaluate for Change's Summer Reading List for Data Nerds https://t.co/8yOfEsYtoZ https://t.co/AxNbJnLVXE

My out of office message this week. https://t.co/TGgK3jInFX

RT @tiff_m_medina: ‘I’m not a great storyteller & this book helped bridge a gap for me.’ Fab review on @evergreendata‘s new book https://t…

RT @TobyHater: Michael: Let's ask PowerPoint. Oscar: Michael this is a presentation tool. Michael: You're a presentation tool.

@abookabouttown saaaaad.

It's here! My new #trunkster suitcase. Zipperless entry, tracking device, charging ports. #travelgeek https://t.co/zgmXEangmv

RT @VizWizBI: Four Ways to Display Timelines by @evergreendata https://t.co/rKXQOXnW57

@mleighwong I use Tableau for mapping. It's pretty nice.

@mleighwong Depends on what features you are looking to replicate!