## 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:

### The Best Office Productivity Tools

#### Kutools for Excel - Helps You To Stand Out From Crowd

 ðŸ¤– Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functionsâ€¦ Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ... Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup... Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List... Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ... Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

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.