I’m not a big fan of lollipop charts. I always find an alternative solution—such as the Dot Plot or the Bar Chart. Stephen Few wrote about the shortcomings of lollipop charts in this blog post. However, if you insist on creating one—because your boss or your client asked you to do so—then this blog post is for you.
In a previous blog post I showed you how to create the following Dot Plot. So I’m going to start off from where we left it and transform the Dot Plot into a Lollipop Chart.
1. I’m going to use Error Bars to display the sticks of the lollipops. To do that, add the following formula into cell E3 to calculate the Positive Error Value
= IF(C3 > 0,0,-C3)
2. Select cell E3 then drag the handle to copy the formula into cells E4:E55. The data source should look like this:
HOW TO CREATE A LOLLIPOP CHART IN EXCEL
Download Excel Model