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.


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!

More along these lines in my next book, set to be released in 2016. Or learn first-hand in one of my upcoming workshops!

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


    • 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!

  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:

  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.

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

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>

@eagereyes @acotgreave @albertocairo @Nsousanis wow all these great people I know, finding each other! ❤️

Swag in the mail! @RadPresenters stickers!

@visualisingdata congrats Andy!

RT @Davidlaz: Any candidate with nothing more than 'prayers' to offer after a mass shooting has no idea how to lead this nation

@AhoyVince I was on Prince Edward Island. Just assumed these things stopped in the 90s!

This still exists.

RT @almack15: So I'm not the only one who gets excited over data visualization. Excellent session today by @evergreendata for CDN Eval Soci…

It's always a good day when I can see the ocean from my workshop room.

@jschwabish it's that 60,000x figure!! @marcusborba @MicoYuk @KirkDBorne