In Excel, you can easily create charts with multiple options available. However, there are many types of displays that require a little tricks then you can create. And in this article I will share with you how to create a lolipop chart.

Ex: a lollipop chart shows the increase in monthly income

To make a chart like this, please refer to the following steps.

1) Prepare the data sheet

You can use your data table or use a structured table like the table below.

Basically, to build a graph of this kind you need two columns of data:

  • Milestone.
  • Milestone metrics.
2) Create a chart

Once you have a table of data, you will then create a chart.

In Excel, you can go to the Insert> Charts> Clustered Column

Once created, the chart will initially not have a lollipop chart but only the basic chart as shown below.

And to the chart of the lollipop form, you have to edit them several operations.

3) Create a lollipop chart in Excel

Continue, select the chart and then on the Ribbon will have more tab Design. Select Add Chart Element> Error Bars> Percentage

At this point, at the top of each column you will see a sign. Double Click on the sign symbol to open the Format Error Bars

Next, customize some of the Vertical Error Bar settings:

  • Direction:
    • Minus
  • End Style:
    • No Cap
  • Error Account:
    • Percentage: 100%

Also in the Format Error Bars, you switch to the Line tab, and customize some of the options like:

  • Color
  • Width
  • Top display lollipop
  • Bottom display lollipop
  • In addition, you can further customize

And when customizing the Format Error Bars is complete, you switch to the Format Data Series. To open the Format Data Series you can Click on the orange part of each column. And with two options:

  • Fill
    • No fill: to hide the orange part.
  • Border
    • No line: to hide the border of the column.

So you have created the lollipop chart in Excel. For a more neat look, you can further customize some of the options:

  • Link the chart name to the chart with Cells
  • Hides or shows elements in Chart Elements.

You can download template by follow link here:

Recent Post

Leave a comment

Your email address will not be published. Required fields are marked *