Easy Dot Plots in Excel

A while ago 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.


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:


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 the very first draft of 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, including simplified instructions.

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.


  1. 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.


    1. 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!

    2. 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

      1. Stephanie Evergreen

        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. 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. 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.

    1. Stephanie Evergreen

      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.

      1. 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.

        1. 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?

          1. Stephanie Evergreen

            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).

  4. 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.

  5. 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.

    1. 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.

  6. 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!

    1. Stephanie Evergreen

      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.

  7. 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.

    1. 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!

  8. 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!

  9. 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.

  10. 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.

    1. Stephanie Evergreen

      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!

  11. 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!

    1. Stephanie Evergreen

      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.

  12. 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.

  13. Seems like your math and language labels are reversed in the final product, compared to the original excel table. Otherwise, very nice tutorial. I’ll be using this technique with my own data.

  14. Thank you for the great post! I was wondering how you might demonstrate significant changes between two values on a dot plot? In the example, it might be nice to note which constructs are significantly different from Fall to Spring. Also, where would you include n values, especially if they differ from Fall to Spring? Thanks again!

    1. I usually don’t include n unless the difference is vast. I also don’t include significance unless I know for sure my audience understands what that means. If they do, you have many options, including a note, color change, line color change, an extra mark, etc.

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.