Dataviz

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!

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

  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.

  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.

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=""> <strike> <strong>

Check out the expanded #dataviz section of @BetterEval that we've been cooking up! http://t.co/Qf85ZhiNgk #eval

Afternoon friends: The rules about when it's ok to NOT start your axis at zero. http://t.co/jDOKX3lP2o #dataviz

RT @esjewett: @evergreendata Yes! Also when the traditional 0-value is no more meaningful than any other value. Temperature, for instance.

RT @AnnKEmery: [New post] Span Charts: When you've only got the min and max, not a mean/median/frequency http://t.co/WQeKFvp0RF http://t.co…

Perhaps a controversial post: When It's Ok to NOT Start your Axis at Zero http://t.co/SdDeI7sHqP #dataviz

RT @parisakharazi: Useful find: Introducing the Data Visualization Checklist - http://t.co/oYPunrz33g Thanks for the great tips! @evergreen

Prepping interactive #dataviz example for workshop w/ K-12 folks next week. Instructions at http://t.co/7Eo7C6BFaN http://t.co/j9bf20iu5W

I'll be in #melbourne Nov 11-15. Grab some coffee with me? Ride a wave?

RT @squishymedia: Are you looking to show data in a compelling way? Take a look at this great read by @evergreendata from @NTEN! http://t.c…

RT @GGorczynski: Declutter your #Dataviz with Small Multiples by @EvergreenData http://t.co/pJY5enQQtW