Custom Charts in Excel :: Butterfly Chart

Posted: July 4, 2013 by MaxFraudy in Excel
Tags: , ,

As you can see from the picture, this chart can be used when you want to compare two entities on same parameters. Here, I’m comparing sales at two stores of some items.

Butterfly Chart

Butterfly Chart

To create the chart, follow these steps:

  1. Set up the data as below. The only columns where you enter data are col [A], [B] and [C].

    Butterfly Chart - Data

    Butterfly Chart – Data

  2. Plot a Stacked Bar chart on this. It will look like this:

    Butterfly Chart - Raw chart

    Butterfly Chart – Raw chart

  3. On this chart do the following:
    1. Change the order of the series to Padding A, Store A, Gap, Store B, Padding B.
    2. Set the maximum of the axis to 225.
    3. Make Padding A, Padding B and Gap series transparent.
    4. Add data labels to Gap series and display Category values instead of values.
    5. Remove the legend.
    6. Remove the gridlines.
    7. Remove the Category labels. Your chart should now look something like this:

      Butterfly Chart - Intermediate

      Butterfly Chart – Intermediate

  4. We now have the bars looking like we want to. What’s left is making the axis correct. For this use the table on the right to plot an X-Y scatter chart as another series on this same chart.
  5. On the resulting chart, do the following:
    1. Set the minimum of the vertical axis as 0 and the maximum to a very high number (~10,000).
    2. Remove the vertical axis.
    3. Add data labels to the X-Y scatter series.
    4. Place the data label position ‘Below’.
    5. Remove the markers.
    6. Add the legend and keep only Store A and Store B series.
    7. Format the chart as you want
Butterfly Chart

Butterfly Chart

Download the file Butterfly Chart

Source: The original post is here.

Advertisements
Comments
  1. Thomas Ellebæk says:

    Awsome!

  2. Amit Lohia says:

    I really liked it but don’t understand some concepts like values in x & y axis . . . . please help me to understand the same

    • MaxFraudy says:

      Hi Amit, thanks for your feedback. I have tried to put in screenshots wherever possible. If you want to understand it better, you can download the excel file and look around.
      Let me know if it’s unclear.

  3. Jon says:

    I am struggling with step number 4. I already exhausted four hours with no luck in finding how to plot with xy scatter on that chart. I will appreciate more details on this.

    Thanks,

    • MaxFraudy says:

      Hi Jon, apologies for the late reply. For step 4, you need to add a new series and then change the chart type to XY scatter. Once you have done this, you can edit the X-series and the Y-series range for the series.
      I hope this helps. Let me know if you need more help.

  4. Sridhar says:

    Jon, I had the same problem. Was stuck at the same point. Max doesn’t explain how to create x-y scatter series; many like us may not know about it. Here is the video that is EXTREMELY helpful. This is for all other who too were stuck at that same point. http://www.youtube.com/watch?v=lVvQOdPr97E

Share your thoughts/feedback

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

WordPress.com Logo

You are commenting using your WordPress.com 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