Butterfly Chart – Excel Chart with Dual Converging Scales




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 !

butterfly-chart

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.

butterfly-chart-data

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.

butterfly-chart-step-1-basic-chart

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.

butterfly-chart-inserting-xy-series

Once we’ve inserted the XY-Series the chart looks like this:

butterfly-chart-step-2-adding-xy-series

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.

butterfly-chart-step-3-aligning-xy-series

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.

butterfly-chart-y-error-bars-gridlines

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.

butterfly-chart-step-4-legend

butterfly-chart-step-5-inverting-the-scale

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.

butterfly-chart

You can download a sample worksheet with a example of Butterfly Chart here or click on the button below:

butterfly-chart


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Jon Peltier wrote:

    The problem with this type of chart (also called a tornado or funnel chart) is that the two series are not directly comparable. The bars have almost the same baseline, but you can’t compare their lengths because they stretch in different directions. I discussed this in Tornado Charts and Dot Plots, in which I proposed various one-sided bar chart variations and finally dot plots..

  2. Jim wrote:

    Ajay – In this butterfly chart, with very small data points (between 0 and 1, such as 0.24, 0.47, and so on), I’m having trouble setting it so the items being measured are in the middle and the scale/gridlines make an even grid.
    I can’t make the padding, gap, label, and x-axis values all work together.
    It’s not your chart; something’s just not penetrating my thick skull! Thanks, Jim

  3. Ajay wrote:

    I get what you are saying. Actually the trick lies in getting a couple of steps right:

    butterfly-chart-step-2-adding-xy-series

    Then try the following:
    1. Change the values for Store A and Store B to say between 0 and 1. The moment you do this, enter a corresponding small value for the Gap as well (say 1 or 0.5). The chart gets very distorted at this point.

    2. Change the values in cell E2 and F2. Reduce them from 100 to say 1.5 or 2. The chart will again begin to look like the one shown above.

    3. Change the Label and X-axis values (column I and J). The “label” values have to be entered as dual – say starting from -1.5 and then moving to + 1.5 with an interval of .25. One by one edit the X-axis values so you force each point to lie on the intersection of the grid lines and bottom x-axis. For the Gap, you may want to give a little more space between the points.

    4. If required, fix the maximum and minimum values for both the upper and lower x-axis scales so that they have the same max and min.

    The remaining steps should be the same. But if it helps, here’s another version of the butterfly chart with smaller scale.

    Hope this helps :-)
    Regards,

  4. Mario wrote:

    Hi,
    I use Excel version of Vista and I cannot find how to set up the labels to the X axis because if I click on data source there is not that options. Could you help me please?
    And also, do you know if it is possible to have more than one series on one side (for example, I need to put two differences variables, one on the left and one on the right, but also for each variable I have two periods, t and t+1, and I want to compare them)?
    Thanks

  5. Sophie wrote:

    A very very very useful and excellent site of sharing the methods of creating chart.
    I visit your website everyday to learn all the special tricks to make great chart.
    Thank you for all your work.
    You have really really helped me a lot.
    Sophie

  6. Avinash wrote:

    Hi Ajay,

    I am trying to create this chart in Excel 2007. But the steps seems to be quite different compared to 2003 version. For example: the steps you mentioned for “Adding the XY series for the dummy scales” – There is option in 2003 for adding two values as X values & Y values for a series. But i couldn’t find a way out to do same in 2007 as it accepts only one value for series. Could you please elaborate the steps for excel 2007 version.

    Thanks,
    Avinash

  1. Custom Charts in Excel :: Butterfly Chart | Useful Gyaan wrote:

    [...] The original post is here. Share this:EmailPrint Pin ItLike this:Like Loading… [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel