Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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

Answer
Discuss

Answers

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.

Discuss

Discussion

Hi Don
Thanks for your comments. I have uploaded an excel file to better explain what I am trying to achieve. 
I look forward to your answer.
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.
Thanks in advance.
Simon
Simon Jul 3, '17 at 2:49 am
My thought would be to make the chart large enough so that everything fits clearly. In the file you sent, the chart was not very big. You can adjust the display position of the labels, but, in my experience some of them always end up looking out of place or don't fit how you want them to fit unless you have a big enough chart or small enough font - remember that you can adjust the appearance of the labels or just of individual ones by clicking on them.

Manually placing lables seems like a bad idea and would be difficult to manage.
don (rep: 1989) Jul 8, '17 at 10:56 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login