Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Register  \/ 
x

or

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

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:

Note: In the above formula, 1200 is the number greater than the largest number of your original data, which is based on your data, if your data is four-digit number like 7800, 8600 and so on, please choose 10000. Please change the number 1200 to the number suitable to your data.

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:

Note: In the above formula, 1200 is the number greater than the largest number of your original data, which is based on your data, if your data is four-digit number like 7800, 8600 and so on, please choose 10000. Please change the number 1200 to the number suitable to your data.

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

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)

Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.