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)
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.
- Click on one of the horizontal bars and select "format error bars"
- Chose Display / Direct / Minus
- 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: