Lollipop chart

The most commonly used charts in Excel are Column and Bar Chart. Although they do the job perfectly, sometimes it is necessary to emphasize the displayed values. For this purpose, you can create a nice chart called “Lollipop” (or Lollypop). It is still not a part of Excel’s standard charts palette and in the following text I will show you how to create one by modifying the Clustered Column chart.

Input data are from a simple table. In the example, several cities with sales values ​​are shown. You need to select a table first, then select the option to create a Column chart from the Insert ribbon. Alternatively, click on Recommended Charts, and then select the All Charts tab and find a column chart.

After adding a chart, go to the Add Chart Element menu and add the Error Bars. In the submenu, select the Standard Error value.

Next you need to go to one of the error lines on the chart, right-clicking the context menu and selecting the option Format Error Bars. On the right side menu will appear where there are options by which we can modify the appearance of the error lines. In the Direction option group, select Minus, End Style should be No Cap, and in the Error Amount group, select the Percentage field and enter a value of 100%. By doing this we have created error lines that range from the column values ​to the x axis.

Then you need to open the Fill & Line menu, select the line to start with the circle and increase the circle. We can also change the color of the error line. The last important step is to set columns to be transparent. As a result, after implementing these actions, we will get an effective Lollipop chart.