A Butterfly chart is a chart where two entities are compared side by side using scales meeting at the center. Due to its shape, the chart resembles a butterfly and hence the name. These charts are sometimes also known as Funnel or Tornado Charts though I find “butterfly” to be a better description as it allows for a greater variation in shape than a funnel or a tornado does !
So let’s jump straight into creating a beautiful looking butterfly chart.
Getting the Data for the chart
Although a simple looking butterfly chart is as easy to create as a bar chart, there is some value in adding labels, converging scales and the other embellishments. They make the chart look so much cleaner and professional and more importantly, help the user get a ‘feel’ of the data faster. For the puropose of this example, let us take the case of a firm called …..what else ….Butterfly Inc. This small has has two stores engaged in the sales of various products. We would like to compare the performance of each of these stores by placing them side by side (I mean the data) and then get a quick grasp of how each one performs compared to the other.
The first three columns essentially contain all the data related to the business. The remaining columns merely help us organize them in the chart. As you might have guessed, the butterfly chart is a stacked bar chart where the various bar series are arranged in such a manner that they meet/align at the center. The padding A and padding B are two special series which simply help us align the actual data series better. What we do is to take a large value (say 100) and then if the actual value of a particular category is, say 45 then its corresponding padding becomes 55 (which is 100-45). We do the same both both the entities – Store A and B in this case. The “gap” is another dummy series that helps us separate the bar and provides a placeholder for the category name/labels. (Biologically speaking – that would be the Thorax of the butterfly !) The last two columns are for creating the ‘special’ axis – where the value of 0 lies at the center and the twin scales proceed outward.
Making the basic Chart
Let’s create a basic chart with five series. By default Excel will plot the series in the order in which they appear in a range. So rather than selecting the entire range (consisting of the first five columns) at one go, we insert one series at a time. We begin with Padding A, followed by values for Store A, then the gap, followed by value for Store B and finally the padding for B.
Adding the XY series for the dummy scales
Excel does not provide the functionality to create an axis which begins at 0 and has two scales extending outwards – something that we do require for creating a butterfly chart. So we crate one on our own. Let’s begin by plotting an XY chart using the last two columns. The series marked as label acts as the Y-Axis and the the other one as the X-Axis. You may want to give this part a bit of focus as the placement of various XY points are determined by the values that you provide here.
Once we’ve inserted the XY-Series the chart looks like this:
Although it may need look much like a butterfly chart, the above pretty much has all the components and is just a few steps away from being one. All that needs to do now is to format the chart.
Aligning the XY points to the X axis
If you noticed, the points are not aligned to the X-Axis. In order to force them to align with the X-axis, you can change the vertical scale towards the right and make the minimum value 0 and provide an arbitrarily large value to the maximum.
Let’s delete to default chart grid lines. We will inset our own custom gridlines by adding the the Y error bars to each of the points.
Adding/Modifying the legend
Let’s turn on the chart legend and place it at the bottom. In order to remove the extra values from the legend, you can select individual named by placing two slow single clicks on it. Once the individual label has been selected, you can use the delete button to remove the label. One by one, apply this step to all the labels that are not required.
The last few steps and our Butterfly Chart is ready to fly
The last few steps are:
1. Turn on the labels for the center bar (use Category name as label)
2. Turn on the labels for the XY Points (use Y-axis as label)
3. Add Title to the chart
4. Remove the marker for the XY points
5. Remove the fill from the first, middle and the last series of bars or fill them with white color.
6. Turn on the labels for the the values for the second and fourth (Store A and Store B) (Use values as labels)
And here is our beautiful butterfly chart.
You can download a sample worksheet with a example of Butterfly Chart here or click on the button below: