How to Create a Tornado (Butterfly) Chart to Display Stacked Bars

0

Hi guys, I am new to the forum and looking forward to your help with my query. I am trying to create a stacked bar butterfly chart which is very challenging and I am not sure if it can be easily done. I have created a butterfly (Tornado) hart in Excel 2013, but I want to make each horizontal bar which is to the right and left sides of the graph, to display the top 4 highest figures that contribute to the overall length of each bar as its sements (i.e. turn the simple bar into stacked bar). Then, the remaining figures (i.e. outside the top 4 max values) for each bar can be summed up and be shown as another segment of the stacked bar. How I have to achieve this.

Example

a1 = 28 (made up of 6, 4, 3, 5, 2, 1, 7) top 4 max figures are: 7, 6, 5, 4 the reminingg summed up to 6 (i.e. 1+2+3)

a2 = 30 (made up of 6, 3, 1, 4, 5, 2, 9) top 4 max figures are: 9, 6, 5, 4 the remaining summed up to 6

b1 = 24 (made up of 3, 1, 4, 5, 8, 1, 2) top 4 max figures are: 8, 5, 4, 3 the remianing summed up to 4

b2 = 17 (made up of 1, 3, 6, 0, 2, 4, 1) top 4 max figures are: 6, 4, 3, 2 the remaining summed up to 2

I would like a1 and a2 to be in the right hand side of the butterfly chart; and b1 and b2 in the left hand side of the chart. Then, for example for a1=28 (horizontal bar)  I would like to be converted to a stacked bar to show its four highest composing figures i.e. 7, 6, 5, 4  as well as the sum of the remaining figures i.e. 3+2+1=6 as the segments f the a1 bar. Therefore, a1 bar now should show 5 segments as a stacked bar. Similarly for a2, b1, and b2 bras in the graph.

I appreciate your expert assistance with this query.

Simon

0

Correct me if I'm wrong, but didn't you use a stacked bar chart to create the butterfly chart in the first place?

If that's the case, you should just be able to list all of the desired values for a few cells by not deleting each element from the stacked bar chart for those cells.

You could add another column to the stacked bar chart and put default values for all cells that you don't want to display it and just delete those parts when it appears on the chart.

That sounds confusing even when I read it back to myself.... if you include a sample file with your question I could more easily help you, or just update that file and include it here.

Update

To create the data segments that you want you will need these formulas:

(these formulas will get you the results that you have in cells A33:F33 and are placed in the cells to the right starting with G33)

=SUM(H33:L33)

=LARGE(C3:I3,1)

=LARGE(C3:I3,2)

=LARGE(C3:I3,3)

=LARGE(C3:I3,4)

=SUMIF(C3:I3,"<"&LARGE(C3:I3,4))

Use these formulas to get the data that you need and then proceed to create your butterfly chart.

Note: since there is no way to know which segment will appear in the top 4, you will not be able to have label for each segment for each bar on the chart; the only way I know off-hand to do that is to create separate charts for each bar and basically change the whole setup of your sheet, which is not really practical.

Discussion

Hi Don
Thanks for your comments. I have uploaded an excel file to better explain what I am trying to achieve.
Simon
Simon Jun 21, '17 at 5:57 am
Many thanks for the answer. I have decided to use two separate horizontal stacked bars which is less complicated. Also, I have identified the labels for each segment (manually) and place them in a data column for inserting them by Excel. But, because the segments are small (close together), labels were overlapped becoming not readable. What is the best way to display them on the graph, or on the gap between the bars? I was thinking maybe to use a text box to contain all labels in one line and insert it in the gap on top of each stacked bar. This method then allow the reader see for example the five labels related to the five segments. But it is manual and difficult to mange when there are about fifty bars. is there a better way for this purpose.