Dot Plots in Microsoft Excel

What’s a Dot Plot?

A dot plot is a simple chart that plots its data points as dots (markers), where the categories are plotted on the vertical axis and values on the horizontal axis.

Line Chart

It’s a nice plot, but it isn’t built into Excel’s default chart offerings. It’s not too hard to make your own Dot Plots, and the Peltier Tech Chart Utility features the Dot Plot as one if its custom chart types.

The Problem

A popular chart type in Excel is a line chart. Below is a line chart showing some simple data. It’s a pretty clean chart, especially without connecting lines between data points. But the labels are hard to read without hurting your neck.

Line Chart

It would be nice to be able to rotate this chart, to make the labels more readable. The only reason not to rotate the chart would be if the categories (Label A, Label B, etc.) were dates, because people are used to time being plotted horizontally. But we can’t simply make a “line chart” in Excel that had the categories on the vertical axis and values on the horizontal axis. The closest we can get is a bar chart.

Bar Chart

But the bar chart doesn’t look as clean, because of all that color. And if there are multiple sets of bars, it can be hard to distinguish the different series of data.

Bar Chart with Multiple Series

Let’s Build a Dot Plot in Excel

It’s possible and not too difficult to construct dot plots in Excel. But it takes a little work. We’ll use an XY scatter chart for the dots, and a bar chart for the vertical axis labels.

The Data

Here is some simple data for our dot plot. The “Labels” column provides the categories for the bar chart, which will adorn the vertical axis. The “Values” column provides the horizontal values for both bar and XY chart. And the “Height” column provides the vertical coordinates for the XY points. The height values are calculated using a simple algebraic formula:

=(ROWS($A$2:$A$17)-ROW()+ROW($A$2)-0.5)/ROWS($A$2:$A$17)

Dot Plot Data

Start the Chart

Select the first and second columns of data and insert a bar chart. I’ve already configured it to be slightly taller than it is wide.

Dot Plot Under Construction

The labels are going in the wrong order, so double click on the vertical axis to open the Format Axis dialog. In Excel 2007 you have so right click on the axis and choose Format Axis… from the pop-up menu (which works in any version). Or in any version you can select the axis, then press Ctrl+1 (numeral one).

Dot Plot Under Construction

Fun tip: Select any Excel object (a cell, a shape, a chart element) and press Ctrl+1 (numeral one) to open the corresponding Format dialog. I think this is my #1 time saving shortcut in Excel, and it ticks me off that other programs don’t use it too. I’m going to harp on you repeatedly until you’ve learned to do it automatically. You’re welcome.

While you’re formatting the vertical axis in the Format Axis dialog, choose Horizontal Axis Crosses At Maximum Category, so the horizontal axis labels return the the bottom of the chart.

Dot Plot Under Construction

Add the XY Data

Select and copy the second and third columns of data, select the chart, and use Paste Special to add the data as a new series, with series in columns, series name in first row, and categories in first column.

Dot Plot Under Construction

Excel adds the data as another bar chart series.

Dot Plot Under Construction

Right click on the new series, choose Change Series Chart Type from the pop-up menu, and select the XY Scatter type. Excel adds a new vertical axis on the right of the chart, since it can’t plot numeric values on the category axis on the right. Fortunately Excel can use the existing horizontal axis for both the bar and XY data.

Dot Plot Under Construction

Align the dots with the bars by formatting* the secondary vertical axis (right side of the chart) so that its minimum and maximum are 0 and 1.

*You used that Select plus Ctrl+1 trick, right? Right?

Dot Plot Under Construction

Format the vertical axis (Ctrl+1!!) so it has no line color and no axis labels, which hides the axis.

Dot Plot Under Construction

Format the bar chart series (don’t make me say it!) so that it has no border and no fill color, which hides the bars and leaves the dots standing alone. I’ve formatted the dots so they have no fill color and a thicker marker line, because I think these look better than boring old filled circles.

Dot Plot

And that’s our Dot Plot.

Adding more data to the dot plot doesn’t clutter it up the way it does with bar charts.

Dot Plot with Multiple Series

Drop Lines May Increase Readability

In this example, the labels are pretty close together, and some of the dots are pretty far away, so it might be tricky for people to tell exactly which dot goes with which label. Often dot plots have lines connecting the dots to the labels to clarify this. We’ll use horizontal error bars for these connecting lines.

Select the dots and add error bars. In Excel 2007 and 2010 find the Error Bars control on the Chart Tools > Layout tab. In Excel 2013 it’s on the menu that pops up when you click the plus sign icon. Choose any of the options, because we need to modify the settings.

Dot Plot Under Construction

For XY points, Excel adds vertical and horizontal error bars. Select the vertical error bars and press Delete.

Dot Plot Under Construction

Format the horizontal error bars, select the Minus direction and the No Cap style option, and for error amount, select the Percentage option and enter 100.

Dot Plot Under Construction

Finally format the error bars, giving them a light line color so they don’t overwhelm the dots.

Dot Plot

 

Peltier Tech Chart Utility

Comments

  1. A cheap nasty way to get a dot plot is to make a line chart, copy the cells it’s embedded in, and Paste Link Picture

    Now rotate the picture 90 degrees, re-arrange the chart title and the axis labels, et voila. (I don’t think I can do anything about the legend)

    Sadly you can’t do this to the chart object, it has to be the worksheet cells you paste link.

  2. Hi Jon, great point.
    I have always used a different approach.
    I do it using an xy series plotted on the secondary axis with a very large value as axis maximum … so:
    1 – Add a serie xy.
    2 – Set x values on the range of labels and y values equal to the other series (can be a simple progression from 1 to n) 3 – Set the serie on the secondary axis and the maximum value of the axis to a very big value 1000000 …
    4 – I add labels to this xy series and set it to show the x values (which is the label text)
    here http://goo.gl/JxilIR a sample file i used

  3. So are you taking the same “Statistics and Data Visualization” class that I am right now? I haven’t seen you in class… Or are you just going through Cleveland’s Visualizing Data? I actually was thinking about how to do dot plots in Excel as my instructor is pushing R at us…

    Next topic: jittering data. Just a suggestion.

  4. Roberto -
    That approach works fine too. I generally find the bar chart easier for people to understand; if the idea of data labels is new to them, they can get confused.
    Using a bar chart was more complicated in Excel versions before 2007, because the bars and scatter points couldn’t share the horizontal axis. When that was changed in Excel 2007, the bar/scatter approach became definitely easier.
    However, the ease of using a range of cells for a set of data labels, introduced (finally!!) in Excel 2013, means the scatter-only approach may soon be the easiest. Even using Rob Bovey’s elegant Cart Labeler add-in was not as easy.

  5. Derek -
    Yeah, that works too, in a quick and dirty way. But I don’t like the quality of the rotated picture. Also, in the old days, having more than about 6 or 8 of these linked pictures could cause Excel to bog down or even crash.

  6. I really enjoyed your previous post on this but it was way over my head. I think I found a way to do one differently.
    What are the disadvantages of the following method?
    Using your “Height” column change all the numbers to 5, rename the column “l’.
    Create a Stacked bar chart, change the color of the charts to No Fill.
    Find the Series l and add data labels, go to format data labels and remove the value and instead add the Series name. Make sure the label is centered.
    Then change the font type to Wingdings and you get a circle. You can change the value in Series l for 5 to something very small like 0.5 so the dots are centered a bit more.

  7. Rossa -
    That’s pretty good, never thought of it. I have a follow-up showing a way to make dot plots using text (in cells, not in charts). This would be an interesting addition to that article, if you don’t mind.
    You can even use a value of 0 for the “l” series, so the labels are as well positioned as it’s possible to position labels. You can play with font color and size, too, as if the dot characters are markers.

  8. Jon,
    Sure no problem, I got the idea after seeing one of your other posts on in-cell charts (where you used the REPT function with a number of spaces followed by a special character).
    The CHAR function used with Webdings, Wingdings1, Wingdings2 & Wingdings3 gives a large number of special characters to play with.

  9. Yeah, I was going to reprise the in-cell charts. Got a few tricks up my sleeves, though.

  10. Hi,
    One of the ways I create dot plots is to use a horizontal bar chart to get the categories. And XY for the actual dots.
    This gives me the flexibility of creating alternate shading by setting alternate rows to be zero or full scale. This helps to guide the eye.
    In most cases the bar fill is light grey, but if specific attention is to be drawn to a set of data, the underlying bar formatting can be changed with a few clicks.
    Many ways to achieve clarity with dot plots.

    Cheers,

  11. I love your new ‘light weight’ writing style in this blog :)

  12. online excel training says:

    This is a neat idea and thank you for the formula tip. Thanks for sharing.

Subscribe without commenting

Trackbacks

  1. […] Peltier explains how to create a Dot Plot chart in Excel. If you make this type of chart frequently, or other complicated chart types, Jon’s […]

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites