You probably know that I’m a big fan of slope charts. And dot plots. Big fan. Love them both. But neither are default chart types in Excel, which, believe it or not, is still the primary data visualization tool for most of the world (I have no data to back that up, but I’m telling you it’s true).
Yesterday, Stephanie Evergreen published a post about how to make a lollipop graph using Excel 2013. If you’re not familiar with the lollipop graph, it’s basically a bar chart except that the end of the bar is replaced with a dot (the candy) and the bar itself is replaced with a line (the stick, if you will). (Alternatively, you might think of it as a dot plot with only one dot and the lines all stretch to the axis.) The lollipop graph reduces a lot of the ink on the page and I think helps the reader focus just on the end where the data are encoded.
Stephanie’s approach to making the lollipop chart in Excel 2013 was to create two scatterplots, one for the points and another for the labels, which were then manually edited one at a time. I use that general approach when I create dot plots in Excel, but I don’t manually insert and edit the labels; that, however, is a post for a different time.
In this post, I want to show you an alternate way to make a lollipop graph. I’ll use Excel 2013 here, but the approach is basically the same on all modern versions of Excel. I’m also going to use the same data Stephanie used in her post.
We’re going to start by creating a simple bar chart.
Now select the bars and add error bars (select the Error Bars option in the Chart Elements menu). In a bar chart, you are only able to add horizontal error bars, so you get the standard-looking thing with an error bar above and below the end of the bar.
Now we’ll format the error bars: In the Format Error Bars menu, select the option for “Minus”, “No Cap”, and set “Percentage” in the Error Amount menu to 100%.
When you set the fill on the bars to No Fill you end up with horizontal lines.
We just need to format the error bars to look like lollipops. So, again in the Error Bar Format menu, set the Join Type to Round and the Begin Arrow Type to the circle (it’s worth noting that Excel 2011 flips what begin and end represent). You can then choose the circle size in the Begin Arrow size menu. You can also change the color in the Color menu in the same menu.
You can then add a title, and format the gridlines, x-axis labels, and y-axis labels as you see fit.
There you go, a lollipop graph. To sum up: (1) build a bar chart; (2) add an error bar; (3) format the error bar with a circle at the end. No need to add another series and edit the series names one at a time. The primary downside of this approach is that the line and the dot are forced to be the same color. But the upside is that this is a much faster approach than creating two scatterplots and editing the series labels by hand.
(Oh, and I should also note that there is an even faster way to make a lollipop using the REPT and & functions, but those are created in the spreadsheet, not as a chart object. Perhaps a separate post? Let me know in the comment box.)
The original draft of this post ended here, but Stephanie’s post and this post on dot plots from Jon Peltier got me thinking: Could you use this method to create a dot plot? Turns out you can. All you need is to calculate the gap between the two series you want to plot and use that as the length of the error bar. You never even plot the second series!
Let me show you. Here, I’ve added a second series–imagine it’s one year and then another year–and I then calculate the gap between the two (the “Error Bar” series).
Selecting the error bars in the lollipop graph above, I go back to the Format Error Bar menu and instead of using 100% in the “Percentage” box, I use the “Custom” box and insert a reference to the “Error Bar” data series in the Negative Error Value box.
In addition to having the Begin style set up as circles, I now change the End style of the error bar to the circle as well.
That left end of the line now has a dot, which looks like I’ve plotted the other data point, but I’ve actually only used an error bar.
Now, this is a pretty decent dot plot in Excel, though there are two things I might like to change. One, I can only easily add data labels to the dot on the right side; I can’t do so with the left side. Second, I might like to have the y-axis labels next to the dot on the left. Both of these can be addressed using a slightly different approach to the dot plot (and that approach will differ between Excel 2013/2016 and Excel 2010/2011). If you’re interested in seeing that approach, please let me know in the comment box below and I’ll write it up soon.
Here’s the Excel file if you’d like to play around.
What do you think? Do you have an alternative way to make these graphs in Excel?