Create butterfly chart in Excel
Butterfly chart is also named Tornado chart which is a type of bar chart composed with a gap in the center showing categories and two bars displayed side by side as below screenshot shown. The butterfly chart is used to compare two sets of data series so that the difference between the two data can be analyzed at a glance. This tutorial will talk about how to create a butterfly chart in Excel quickly and easily.
- Create a butterfly chart in Excel
- Download Butterfly Chart sample file
- Video: Create butterfly chart in Excel
Create a butterfly chart in Excel
Supposing, you have the following data range to create a butterfly chart based on, please do with the below steps for creating butterfly chart in Excel.
First, create the helper columns data
1. You need to insert three columns to your original data, insert the first helper column to the left of product Apple, then insert the second helper column between the two products, at last, insert the third column to the right of the product Peach as below screenshot shown:
2. Please type this formula =1200-C2 into cell B2, and then drag the fill handle down to the cells as following screenshot shown:
3. Then, in the inserted column D, enter a list of number 500 which is used as the gap space between the two bars of the chart, you can replace the number 500 with your own based on your data. See screenshot:
4. At last, in the column F, enter this formula: =1200-E2 into cell F2, and then drag the fill handle to copy this formula to other cells, see screenshot:
Second, create the chart based on the data
5. After getting the helper column data, please select all the data range (A1:F8), and then, click Insert > Insert Column or Bar Chart > Stacked Bar, see screenshot:
6. And a stacked bar chart has been inserted as below screenshot shown:
7. Then, you can delete the unneeded elements from the chart, such as the chart title, gridlines and useless legend, see screenshot:
8. Next step, you should format the helper column data series bars with no fill.
9. Right click the left padding data series (the blue bars), and then choose No Fill from the Fill drop down list, see screenshot:
10. Then, repeat the above step to format the gap and right padding data series as no fill to get the chart as below screenshot shown:
11. Then, click to select the gap bars, and then click Chart Elements > Data Labels > More Options, see screenshot:
12. Go on right clicking the data labels in the chart, and then choose Format Data Labels from the context menu, see screenshot:
13. In the opened Format Data Labels pane, under the Label Options tab, check Category Name and uncheck Value option, and you will get the chart as below screenshot shown:
14. And then delete the vertical axis, then right click the horizontal axis, and then choose Format Axis, in the opened Format Axis pane, under the Axis Options tab, reduce the Maximum bound to 3000, please type the value based on your data, see screenshot:
15. If the bars are too narrow for you, you can adjust them wider, right click the bars, and then choose Format Data Series, see screenshot:
16. In the Format Data Series pane, under the Series Options tab, change the Gap Width to 80% as you need, see screenshot:
17. Now, you should insert the data labels for the chart, click to select the Apple data series (the orange bars), and then click Chart Elements > Data Labels > Inside Base, see screenshot:
18. Go on clicking to select the Peach data series (the golden bars), and then click Chart Elements > Data Labels > Inside End, see screenshot:
19. Now, the butterfly chart has been created successfully as below screenshot shown:
Download Butterfly Chart sample file
Video: Create butterfly chart in Excel
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
