In our last Friday Challenge, I proposed making a Double Lollipop Chart seen in the Wall Street Journal (WSJ).
Here is what our final chart will look like:
Hopefully, the challenge didn’t throw you for a loop (or a lollipop). So let’s get to the solution:
The Breakdown
1) Update Date Format
2) Add Zero Series
3) Create Line with Markers Chart
4) Create Chart Title
5) Move and Clean Up Legend
6) Modify Vertical Axis
7) Modify Horizontal Axis
8) Move Zero Series to Secondary Axis
9) Change Line and Marker Formats
10) Add High-Low Lines
Step-by-Step
1) Update Date Format
Here is our original chart data series: 
To get the chart to look like the WSJ chart, the first thing we need to do is to update the format of the Year. Since it is a full date, we just need to create a custom number format. So highlight the year dates from A2:A16. Then select the More Number Formats… menu option from the Number Format picklist in the Number group of the Home Ribbon: 
Then type in a single quote followed by yy in the Type box of the Format Cells dialog box:
Your data series will now look like this:
2) Add Zero Series
Now if you look at the WSJ chart at the top of the post, you will notice that the gridlines are dashed and not a straight line. So as we try and replicate the chart, we will need to add a chart series to make the zero gridline a straight black line and not a dashed line.
So in column D, let’s create another data series called Zero.
Then put a value of zero (0) in cells D2:D16. Your data series should now look like this:
3) Create Line with Markers Chart
Now that we have all our chart data series set up, we can create our chart. Highlight cells A1:D16 and then go to your Insert Ribbon.
From the Insert Ribbon, choose the Line button and then the Line with Markers from the 2-D Line chart options:
Your chart should look like this:
4) Create Chart Title
I am sure you have all done this many times before, but we want to add a chart title to the WSJ Lollipop Chart. Simple select your chart, then click on the Layout Ribbon and the the Chart Title button and the Above Chart menu option.
Then type in “Running Behind the Bulls” and press Enter. Your chart will now look like this:
5) Move and Clean Up Legend
If we compare our chart as it stands now versus the original WSJ chart, we see that the legend is not in the right place and it has an extra item that we don’t want (Zero). So lets move it and clean it up. To delete the Zero legend item, select the chart, then select the legend, then select the Zero legend item. Then press your delete key. Then select the Layout Ribbon and the Legend Button and then the “Show Legend at Top” to move the legend.
Your chart should now look like this:
6) Modify Vertical Axis
To make our chart look like the WSJ chart, we need to modify it slightly. Right Click on on the Vertical Axis and choose Format Axis.
Then change these options from the Axis Options menu:
Then change the Line Color to No Line:
Your chart should now look like this:
7) Modify Horizontal Axis
Now lets modify the Horizontal Axis to match the final chart output. Right Click on on the Horizontal Axis and choose Format Axis.
Then change these options from the Axis Options menu: 
Then change the Line Style to Round Dot:
Your chart should now look like this:
8) Move Zero Series to Secondary Axis
We are getting really close. Now we need to move the Zero data series to the secondary axis. Otherwise our final step won’t give us the desired effect. So right click on the Zero data series and then click on Format Data Series… and then move it to the Secondary Axis.
Your chart should now look like this:
Now we don’t need the Secondary Vertical Axis. So select it with your mouse and press the delete key. Your chart will then look like this:
9) Change Line and Marker Formats
This step will get us very close the the final chart. We need to remove the line and change the marker formats.
a) Average Forecast line:
To do that, we need to right click on Average Forecast line and select Format Data Series… and change the Marker Options to a Built-in Type of a Circle:
Then change the Marker Fill to Solid fill and a Color of White:
Then change the Line Color to No Line:
Then change the Marker Line Color to Solid Line with a color of Red:
Your chart will now look like this:
b) Actual Gain line:
Right click on Actual Gain line and select Format Data Series… and change the Marker Options to a Built-in Type of a Circle:
Then change the Marker Fill to Solid fill and a Color of Red:
Then change the Line Color to No Line:
Then change the Marker Line Color to Solid Line with a color of Red:
Your chart will now look like this:
c) Zero line:
Right click on Zero line and select Format Data Series… and change the Marker Options to None:
Then change the Line Color to Solid Line with a color of Black:
Then change your Line Style to a Width of 0 (zero)
Your chart should now look like this:
10) Add High-Low Lines
This is the last step in the Excel Chart Tutorial. We just need to add a line between each lollipop. To do that, select the chart. Then go to your Layout Ribbon. Then select the High-Low Lines option from the Lines button.
Now your final chart should look like this:
Looks almost identical to the original Wall Street Journal chart in Excel.
Video Demonstration
Free File Download
How-to-Make-a-WSJ-Double-Lollipop-Chart.xlsx
Would you use this chart in your business? Let me know of any use cases in the comments below.
Steve=True
