How-to Make a Wall Street Journal (WSJ) Double Lollipop Chart in Excel

In our last Friday Challenge, I proposed making a Double Lollipop Chart seen in the Wall Street Journal (WSJ).

Learn Excel Dashboard Course

 

Here is what our final chart will look like:image

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: image



Learn Excel Dashboard Course

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: image

Then type in a single quote followed by yy in the Type box of the Format Cells dialog box:SNAGHTML1fb48f

Your data series will now look like this:image

 

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:image

 

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:image

Your chart should look like this:image

 

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.image

Then type in “Running Behind the Bulls” and press Enter.  Your chart will now look like this:image

 

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.



Learn Excel Dashboard Course

image

Your chart should now look like this:image

 

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:SNAGHTML3e7178

 

Then change the Line Color to No Line:SNAGHTML3d6690

Your chart should now look like this:image

 



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: SNAGHTML416e0f

Then change the Line Style to Round Dot:image

Your chart should now look like this:image





 

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.SNAGHTML453c65

Your chart should now look like this:image

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:image

Learn Excel Dashboard Course

 

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:SNAGHTML539270





Then change the Marker Fill to Solid fill and a Color of White:SNAGHTML551140

Then change the Line Color to No Line:SNAGHTML562240

Then change the Marker Line Color to Solid Line with a color of Red:SNAGHTML572a8a

Your chart will now look like this:image

 

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:SNAGHTML539270

Then change the Marker Fill to Solid fill and a Color of Red:SNAGHTML5af2d8

Then change the Line Color to No Line:SNAGHTML562240

Then change the Marker Line Color to Solid Line with a color of Red:SNAGHTML572a8a





Your chart will now look like this:image

c) Zero line:

Right click on Zero line and select Format Data Series… and change the Marker Options to None:SNAGHTML5db636

Then change the Line Color to Solid Line with a color of Black:SNAGHTML5f4950

Then change your Line Style to a Width of 0 (zero)SNAGHTML6084ba

Your chart should now look like this:image

 

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.image

Now your final chart should look like this:image





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





6 COMMENTS

  1. Just an observation. I noticed that your lines are slightly off-center to the left of the markers, while with my solution the lines are slightly off-center to the right. The WSJ chart lines meet at the center of the markers. If the analyst at the WSJ used Excel, I would wager that since this was probably a onetime chart for him/her, and the analyst knew the editor would send the chart back to align the lines and markers, the lines were simply drawn in with the line option from shapes.
    Or, maybe the WSJ analyst did use Excel and figured out a way to align the markers and lines. If I was off-center to the right and you were off-center to the left, maybe there is a way. I could not figure it out. A new challenge maybe?

LEAVE A REPLY

Please enter your comment!
Please enter your name here