How to create a double lollipop chart

Visually great charts are always welcome in my inbox, so when a friend forwarded a link to me showing a tutorial on how to create a double lollipop chart, I was intrigued.  A double lollipop chart (not to confused with a cherry chart – although it looks very similar) is used to compare target vs actual performance – it’s another way of comparing one line against another.

lollipop chart WSJ

The picture above is from the post I was sent, which had a great tutorial created by SteveEqualsTrue from and a download which recreated the chart (above) seen in the Wall Street Journal.  Since I didn’t want to recreate the WSJ chart and actually wanted the chart to have conditional formatting, I created my own version.

cool chart

You can download the sample chart here.

 Step 1: Set up your data

To begin with you should have a data table with one column showing the actual performance, and the second column showing the target figures for each of your datasets (time, categories).

actual vs target data table

To add in some conditional formatting, we are going to add 2 new columns titled “< target” and “> target” as shown in the picture below:

CF target columns

In cell C2 of the “< target” column type in the calculation:


In cell D2 of the  “> target” column type in the calculation:


These two calculations assume that your actual data is in column B, your target data is in column E and your “< target” and “> target” data will be put into columns C and D respectively. Basically in column C, if the value in the actual column is less than or equal to the value in the corresponding row of the target column, it will return the value in column B, otherwise it will show a #N/A value instead.  The same principle applies for Column D only it looks to see if the value is greater than the target.

Copy and paste the calculations down the rows to fill the table.  It should now look something like this:

calculations complete

To finish the table we are going to create an axis column.  This is because some of our values are less than zero and we will need to create a custom horizontal axis at the bottom of our chart that won’t interfere with our data. The lowest value is -50%, so I’m going to add a column called “Axis” and fill each row with the value -60%.

custom axis

Step 2: Create the chart

Highlight cells C1:F9.  Then with the cells selected, hold down the Ctrl key and then use your mouse to also select cells A1:A9.  Then click on Insert > Charts > Line > 2-D Line > Line with markers.

insert line chart with markers

A chart like the one below should appear:

line chart

You can delete the gridlines now or later.  Double click on the legend and then click on the Axis label and press the delete key.

updated line chart

Step 3: Format the Axis series

Left click on the Axis series.  Then press Ctrl+1 to open up the formatting menu box. Change the series so that it is plotted on the Secondary axis.

secondary axis

Then with the formatting menu still open, click on the marker options tab and choose to have no markers (none).  Finally click on the Line Color tab and choose to have no line.  You can then press the close button.  Your chart should have updated as follows:

secondary vertical axis

Step 4: Format the axes

Before we format any of the axes, we need to add a secondary horizontal axis.  To do this, click on the chart, and then click on Chart tools > Layout > Axes > Axes > Secondary horizontal axis > Show left to right axis.

secondary horizontal axis

Your chart should update as shown:

all axes

Left click on the primary horizontal axis and press Ctrl+1. Remove the tick marks (major and minor) and the axis labels.

format horizontal axis

Then click on the Line color and Line style tabs and adjust the formatting as you wish.  Your chart should update as you make the changes:

formatted horizontal axis

With the formatting menu box still open, left click on the secondary vertical axis. Remove the tick marks (major and minor) and the axis labels. Then check the “Values in reverse order” check box.

values in reverse order

Finally, click on the Line color tab and choose No line.

Your chart should update as shown below:

finished axes

You can now press the Close button.

Step 5: Create the double lollipops

Left click on the “Target” series.  Then click on Chart tools > Layout > Analysis > Lines > High-Low lines.

high low linesYour chart should update as shown below:

high-low lines

Reselect the target series and press Ctrl+1.  Click on the Line color tab and choose No line.  Before closing the formatting menu box, repeat  the process for the “< target” series and the “> target” series.  Your chart should update as shown below:

basic lollipop chart

To finish, create shapes for your bullets.  Copy the first shape using Ctrl+C.  Then click on the series you want to use the chape for and press Ctrl+V.  I’ve used circles as shown below.  Green means we’ve achieved more than the target, red means below target.  I’ve also formatted the high-low lines.

cool chart

However you can format the lollipops anyway you choose, including using triangles (to look like up and down arrows).

alternative lollipop chart

You can also plot the data without conditional formatting:

double lollipop chartAnd that’s it.  I hope you find it useful!  You can download the sample chart here.

+Alesandra Blakeston

2 thoughts on “How to create a double lollipop chart

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s