Eric in Miami asked me how to create a butterfly chart in Excel. After trying to figure out what a butterfly chart is, I realized he wanted what I call a paired bar chart. Starting in Excel 2010, the simple way to do this is with Data Bars in conditional formatting.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1933.
Butterfly Chart in Excel.
Now, every time I’m doing one of my Power Excel Seminars, I walk around the room beforehand, to see if people have any specific questions.
And a guy named Eric say: “Hey, I need to create a Butterfly Chart in Excel.” I’m thinking: a Butterfly Chart?
What the heck is a Butterfly chart?
And I'm going to think about some sort of an X-Y scatter chart in the shape of a butterfly.
I’ve never heard of the terms “Butterfly Chart”.
So I, you know, I said: Eric, tell me more about what you're trying to do.
And he describes something that I know, or what I call as a Paired Bar Chart.
I said: Hey, you know, starting in Excel 2010, we have a great way to do that using not a regular chart, not an X-Y chart, but a conditional formatting.
So, he wanted to compare both, Pounds and Dollars for these Categories.
And so I build a little table over here to grab those values.
So the Category is going to get on the middle (=A2), on the left hand side we're at Pounds, so =B2, and on the right hand side Dollars, like that (=C2).
So I’m going to copy that whole thing down, through our data set.
And then, over here on the right hand side, I'm going to create the right side of the Butterfly Chart, by using HOME, Conditional Formatting and Data Bars.
I’m going to just choose data, you know, any color, like that.
And then here's the thing.
Now, we could have done this part in Excel 2007, but there's one important setting: that was not available.
So Conditional Formatting, Data Bars and choose a different color for the left side, all right.
But Eric said “No, no, no, that's not what I need.
I need those things on the left hand side to start from the middle.” I said: yeah, that's the beautiful thing here.
So we go to Conditional Formatting, Manage Rules and Edit this Rule.
And starting at Excel 2010, there's this thing called Bar Direction, where we can change from Context to Right-to-Left, all right.
And then, also, for both sides, we're going to choose Show Bar Only.
Click OK, click OK, and then the same thing over here.
Select those, Manage Rules… on the right hand side they're already going the right direction, we just want to say Show Bar Only.
Click OK, click OK, all right.
So now we have the various categories and you can see the various amounts going in two sides.
Kind of like a butterfly.
That's why call it a Butterfly Chart.
I've seen this before as a Paired Bar Chart, where, I know I have it in one of my books, the “Charts and Graphs” book.
We have different flavors of ice cream and then male and female.
Or it was actually adults and kids, so, you know, the kids tend to like the simple flavors, whereas the adults were going after the more complex flavors.
And so, that allows you to show those two sets of percentages.
The interesting thing here is, even though that these numbers are of different orders of magnitude, you can still kind of get a sense for, you know, which items have the largest magnitude.
Like, for some reason, down here there's a lot of Pounds but not a lot of Dollars in those products.
So you can kind of spot those.
There you go, for Eric in Miami, how to create a Butterfly Chart in Excel.
Alright, hey, I want to thank you for stopping by, I’ll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1933.
Butterfly Chart in Excel.
Now, every time I’m doing one of my Power Excel Seminars, I walk around the room beforehand, to see if people have any specific questions.
And a guy named Eric say: “Hey, I need to create a Butterfly Chart in Excel.” I’m thinking: a Butterfly Chart?
What the heck is a Butterfly chart?
And I'm going to think about some sort of an X-Y scatter chart in the shape of a butterfly.
I’ve never heard of the terms “Butterfly Chart”.
So I, you know, I said: Eric, tell me more about what you're trying to do.
And he describes something that I know, or what I call as a Paired Bar Chart.
I said: Hey, you know, starting in Excel 2010, we have a great way to do that using not a regular chart, not an X-Y chart, but a conditional formatting.
So, he wanted to compare both, Pounds and Dollars for these Categories.
And so I build a little table over here to grab those values.
So the Category is going to get on the middle (=A2), on the left hand side we're at Pounds, so =B2, and on the right hand side Dollars, like that (=C2).
So I’m going to copy that whole thing down, through our data set.
And then, over here on the right hand side, I'm going to create the right side of the Butterfly Chart, by using HOME, Conditional Formatting and Data Bars.
I’m going to just choose data, you know, any color, like that.
And then here's the thing.
Now, we could have done this part in Excel 2007, but there's one important setting: that was not available.
So Conditional Formatting, Data Bars and choose a different color for the left side, all right.
But Eric said “No, no, no, that's not what I need.
I need those things on the left hand side to start from the middle.” I said: yeah, that's the beautiful thing here.
So we go to Conditional Formatting, Manage Rules and Edit this Rule.
And starting at Excel 2010, there's this thing called Bar Direction, where we can change from Context to Right-to-Left, all right.
And then, also, for both sides, we're going to choose Show Bar Only.
Click OK, click OK, and then the same thing over here.
Select those, Manage Rules… on the right hand side they're already going the right direction, we just want to say Show Bar Only.
Click OK, click OK, all right.
So now we have the various categories and you can see the various amounts going in two sides.
Kind of like a butterfly.
That's why call it a Butterfly Chart.
I've seen this before as a Paired Bar Chart, where, I know I have it in one of my books, the “Charts and Graphs” book.
We have different flavors of ice cream and then male and female.
Or it was actually adults and kids, so, you know, the kids tend to like the simple flavors, whereas the adults were going after the more complex flavors.
And so, that allows you to show those two sets of percentages.
The interesting thing here is, even though that these numbers are of different orders of magnitude, you can still kind of get a sense for, you know, which items have the largest magnitude.
Like, for some reason, down here there's a lot of Pounds but not a lot of Dollars in those products.
So you can kind of spot those.
There you go, for Eric in Miami, how to create a Butterfly Chart in Excel.
Alright, hey, I want to thank you for stopping by, I’ll see you next time for another netcast – MrExcel.
