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.
The picture above is from the post I was sent, which had a great tutorial created by SteveEqualsTrue from exceldashboardtemplates.com 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.
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).
To add in some conditional formatting, we are going to add 2 new columns titled “< target” and “> target” as shown in the picture below:
In cell C2 of the “< target” column type in the calculation:
=IF(B2<=E2,B2,NA())
In cell D2 of the “> target” column type in the calculation:
=IF(B2>E2,B2,NA())
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:
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%.
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.
A chart like the one below should appear:
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.
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.
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:
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.
Your chart should update as shown:
Left click on the primary horizontal axis and press Ctrl+1. Remove the tick marks (major and minor) and the axis labels.
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:
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.
Finally, click on the Line color tab and choose No line.
Your chart should update as shown below:
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.
Your chart should update as shown below:
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:
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.
However you can format the lollipops anyway you choose, including using triangles (to look like up and down arrows).
You can also plot the data without conditional formatting:
And that’s it. I hope you find it useful! You can download the sample chart here.
User Friendly 