Excel - Butterfly Chart

Custom Charts in Excel :: Butterfly Chart

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

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
2. Plot a Stacked Bar chart on this. It will look like this:
   

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