Excel Dot Plot Charts

Dumbbells and Lollipop Charts

Excel Dot Plots, dumbbells and lollipop charts are good for comparing one, two or three points of data. For example, year on year, before or after or A vs B.

They make a nice change from a column or bar chart (like the one below) and are less cluttered:

Column and bar charts also require the horizontal axis to begin at zero. This is because we instinctively compare the length of the columns/bars and make judgements based on the difference in size. If we don’t start the bar lengths at zero we can falsely exaggerate the difference and mislead our audience.

Take the following example where the horizontal axis starts at $600M:

Department A’s 2017 sales target appears to be double that of 2016, but in fact it’s only 23% more. Department C is even more misleading.

This is why we must always start bar and column chart axes at zero.

However, dot plots:

…and dumbbell charts (below), aren’t bound by this rule because the dots aren’t connected to the vertical axis base line:

And so, our eye isn’t drawn to make comparisons in the distance from the vertical axis. Instead we judge them based on the position along the horizontal axis.

This allows us to emphasise the difference between the dots, whether that be two points as in the dumbbell charts above, or the single points in the dot plot.

Lollipop Charts

Lollipop charts get their name from the leader line that draws your eye to the dot, and because of this I think you should start your axis at zero for the same reasons we do with bar and column charts:

Dot Plots, Dumbbells and Lollipops - Which is Best

All charts are useful, and your choice will depend on the points you want to emphasize:

Bar/Column Charts – quickly compare the size of one department to the next and compare from one period to the next within that department

Lollipop Charts – a less cluttered take on the bar chart. Make sure the axis starts at zero.

Dumbbell Charts – emphasize the change from one point to the next with some comparison between departments

Dot Plot Charts – allow comparison between departments with more emphasis on the difference

Watch the Video

Download the workbook

Building Excel Dot Plot Charts

Start with your data structured like so:

Tip: The spacing simply assigns each department to a row in your chart so they’re nicely vertically distributed. You can change the spacing to suit your needs.

1. Select the data in columns B and C > Insert tab > Scatter Chart. It should look like this:

2. Right-click the chart > Select Data > Edit the series name so it points to cell B71 that contains the year name:

 

3. Edit the vertical axis (right-click > format or left-click > Ctrl+1) > Set the maximum to 2.5 (or to match the maximum spacing value in your data set).
4. If you want to emphasize the difference between the dots you can set the horizontal axis minimum to something closer to the lowest value in your data set. I’ve set mine to $600M.
5. Turn off the vertical axis (just select it and press DELETE).
6. Turn off the vertical gridlines (select them and press DELETE). Optionally also turn off horizontal gridlines.
7. Select the dots > CTRL+1 to format > Marker > Marker Options > set the marker options, type and size (see my settings below -# 1). Set the fill to white (#2 & #3) and make the border thicker (#4 & #5):

8. Add labels > align them left and format them to display the X value. If you have Excel 2013 or Excel 2016 you can also include the Department names using the ‘Values From Cells’ reference as shown below:

Note: If you have an earlier version of Excel then you can use the technique described by Jon Peltier here to assign your department names to the vertical axis labels.

Or you can get Jon's Excel add-in that can create dot plots and more:

Peltier Tech Chart Utility – available for PC and Mac

 

9. If you want to keep the horizontal gridlines, then you can fill the labels with white (select labels > Format tab) so that the line doesn’t strike through the label:

 

Excel Lollipop Charts

Lollipop charts require the same steps as the Dot Plot, but you delete the horizontal gridlines and replace them with error bars. To add error bars:

1. Select the dots > Chart Tools > Design > Add Chart Element > Error Bars > Percentage. Then open More Error Bar Options from the same menu.
2. Set the direction to Minus, End Style to No Cap and Error Amount is Percentage at 100%:

Excel Dumbbell Charts

Dumbbell Charts (sometimes called DNA charts), require the same steps as the Dot Plot. Then you simply add a second series:

1. Right-click chart > Select Data > Add Legend Series
2. Select the second set of data for the X series, in my case it’s 2016 data. The Y series are the Spacing values:

3. Add error bars (note: the Error Bars are based on the difference between 2017 and 2016 and you can see the calculation in column E) – Select the 2016 dots in the chart > Chart Tools > Design tab > Error Bars > More Error Bar Options. This will open the Error Bar formatting dialog box or pane (shown below):

 

4. Click on ‘Specify Value’ and select the Positive Error Values from the table:

Tip: If you have negative error values (like the example below), then you’ll also need to add a column to your table to calculate them and then reference those cells in the ‘Negative Error Value’ field shown in the dialog box above.

Be sure to download the Excel file to see the example above.

Camera Tool Alternative

If you’re familiar with Excel’s camera tool, then a quick and dirty way to create a dot plot is to insert a line chart with only markers and use the Camera tool to rotate it on it’s side.

However, often the image in the camera tool isn’t as crisp as you might like, and if you insert too many of them then Excel might have a tantrum and crash.

 

References

Jon Peltier: https://peltiertech.com/dot-plots-microsoft-excel/

Naomi Robbins: http://www.b-eye-network.com/newsletters/ben/2468

Stephanie Evergreen: http://stephanieevergreen.com/easy-dot-plots-in-excel/

Please Share

If you liked this please click the buttons below to share.