Home > Blog

Enhancements to a Dot Chart - lines to the axis labels

In this enhancement to the original dot chart post this blog shows how to draw lines linking the axis to the dots.

If you don’t want to label the dots you can increase readability, especially if your category names are long, with lines drawn from the dot to the axis. To do this we use horizontal error bars.  Note this doesn’t work if your horizontal axis is a date line

Click on the dots to select the data series and add error bars. Different versions of excel give different ways of doing this (2007-10 this is a button on the Chart tools / layout /error bars and in later versions the error bars are in Chart Design / Add Chart Elements)

old style

latest style

This generates a mess of vertical and horizontal lines (note that in later versions you can be more selective about the lines that are added)

Select one of the vertical lines and hit <DEL>.  Hint: If you display the format pane it is possible to select individual elements of the chart to format by clicking on the drop down and selecting "Vertical Error bars" and then hitting <DEL>.  This avoids the need to carefully select them with the mouse.

Now you need to edit the error bar so that only the negative (i.e. left hand) bar is shown and it only goes back to the Y Axis.

  1. Click on one of the horizontal bars and select "format error bars"
  2. Chose Display / Direct / Minus
  3. Chose "percentage" option and enter 100.  Note that if you have negative values the bar will extend the wrong way for these.  In this case chose "custom" and don't enter a value


Chose line colour and select a colour which doesn't compete with the rest of the chart and you have lines connecting the labels on the axis with the individual dots:

final result

Miles Goodchild

Miles Goodchild has been a Program and Programme Planner, PMO lead and Project Manger since 1998.  He enjoys using MS Office, especially Project, Excel and Visio to make life easier and simpler.  In the course of this he created SummaryPro.  In this blog he shares some of the tips and techniques he has learnt over the years in the hope that they will be useful to you.