How to Create Butterfly Chart in Excel (2 Easy Methods)

The Butterfly Chart is mainly used to compare two dataset variables with a horizontal bar graph. It is also called the Tornado Graph because of the similarity in shape. In this article, we will learn how to create a butterfly chart in excel with 2 easy methods.

Download Workbook

Download the sample file and practice by yourself.

2 Easy Methods to Create Butterfly Chart in Excel

For illustration, here is a dataset representing the values of half-yearly sales in the years 2020 and 2021 of a company.

Let’s make a butterfly chart out of this dataset following these 2 methods.

Method 1: Create Excel Butterfly Chart with Padding Columns

In this first method, we will insert padding columns along with the dataset to create a butterfly chart. For this, follow the steps carefully.

Step 1: Insert Padding Columns from Dataset

To start this process, initially, we need to insert padding columns inside the dataset. Follow the process below:

• First, insert 3 new columns beside columns Month, 2020 and 2021.
• Then, name the columns Left Padding, Gap and Right Padding respectively.

• Next, insert this formula in cell C5.

Here, we have taken the value of 11,500 because it is greater than the highest value of this dataset. You can take any other value greater than the highest value of your dataset.

• After this, press Enter.
• Now, drag the bottom corner of cell C5 up to cell range C6:C10.

• Following, insert the value of $ 4,500 in cell range E5:E10.

Here, we used $ 4,500 as the Gap value because it is closer to the lowest value in the dataset. You can use any other value closer to that.

• Lastly, insert this formula in cell G5.

• Then, press Enter.
• Lastly, use the AutoFill tool to imply the formula in cell range G6:G10.

Step 2: Create Initial Bar Chart

At this step, let’s create the initial bar chart from the prepared dataset. Follow the process below:

• First, go to the Insert tab and select the Bar Chart.

• Then, select the Stacked Bar from the drop-down menu.

• Finally, you will get the initial bar chart.

• Now we will format this bar chart to create a butterfly chart.

Step 3: Format Chart to Create Butterfly Chart

In this last step, we will do some formatting to make our desired butterfly chart. Let’s see the process below:

• Firstly, select the Left Padding bar and right-click on it.
• Secondly, choose No Fill from its Context Menu.

• Similarly, apply No Fill to Gap and Right Padding bars as well.
• Along with it, remove the names of these padding columns from the Legend.
• Now, the chart looks like this:

• After that, mark the Data Labels option check in the Chart Elements section.

• Following, right-click on the middle bar and select Format Data Labels.

• Here, select the Category Name and deselect Value from the Label Options.

• Along with it, remove the values of the left and right padding columns.
• Lastly, delete the vertical axis and select the horizontal axis to format.
• Here, set the Maximum value to 28000 or a logical value compared to the dataset.

• Finally, we have our required butterfly chart based on the dataset.

Read More: How to Set Intervals on Excel Charts (2 Suitable Examples)

Similar Readings

• How to Make Sankey Diagram in Excel (with Detailed Steps)
• Remove Last Modified By in Excel (3 Ways)
• How to Make a Venn Diagram in Excel (3 Easy Ways)
• Move Up and Down in Excel (5 Easy Methods)

Method 2: Make Butterfly Chart by Formatting Axis in Excel

In this method, we will create a butterfly chart working on the axes. Let’s follow the steps below:

Step 1: Insert Primary Bar Chart

Initially, we will insert a regular bar chart from the dataset.

• First, select cell range  B4:D10.
• Then, go to the Insert tab and select the Bar Chart.

• Here, select the Clustered Bar.

• Finally, we get our initial bar chart like this:

Step 2: Format Bar Chart Axes

Now let’s format the axes of the chart we made.

• In the beginning, select the blue bars and right-click on them.
• Therefore, choose Format Data Series from its Context Menu.

• Then, enable the Secondary Axis option.

• Now, select the secondary axis and set the Bound value relatable to the dataset.
• Also, mark Values in reverse order checked.

• After that, scale the primary axis value as well.

• Lastly, remove the secondary axis and it will look like this:

Step 3: Edit Bars to Make Butterfly Chart

Now,  we will proceed to create the butterfly chart following these steps:

• First, right-click on the vertical axis and select Format Axis from the Context Menu.

• Now, set the Label Position to Low.

• Lastly, activate the Data Label option and make the position Inside Base.

• Finally, we have our required butterfly chart.

Read More: How to Create an Organizational Chart in Excel from a List

How to Create In-Cell Butterfly Chart in Excel

If you want the butterfly chart inside your dataset then this method will help you profoundly. Check the steps below:

• First, select cell range B4:D10.

• Then, go to the Data tab and click on Sort under the Sort & Filter group.

• Next, modify the Column section as per the image.

• Now, select cell range C5:C10.
• Then, go to the Home tab and click on Conditional Formatting.

• Following, go to the Data Bars and choose More Rules.

• At this point, change the Type and Value based on the selected cells like this:

• Along with it, modify the Bar Appearance as per your preference.
• Also, change the Bar Direction to Right-to-Left.

• Finally, we have our first set of bars in the dataset.

• Apply the same procedure for cell range D5:D10.
• In this case, make the Bar Direction as Left-to-right.
• That’s it, we have our final output.

Read More: How to Show Menu Bar in Excel (2 Common Cases)

Things to Remember

• Always organize your dataset in descending order to get the properly shaped butterfly chart.
• Make sure the values are in the same format.
• A butterfly chart is only created with two variables, not more than that.

Conclusion

I hope it was an efficient article for you on how to create a butterfly chart in excel with 2 easy methods. Let us know your feedback on this. Follow ExcelDemy for more blogs like this.

Related Articles

• How to Add Trailing Zeros in Excel (2 Easy Ways)
• Calculate WACC in Excel (with Easy Steps)
• How to Make a Box Plot in Excel (With Easy Steps)
• Make a Forest Plot in Excel (2 Suitable Examples)
• How to Calculate Cost of Funds in Excel (with Easy Steps)
• Make a Modified Box Plot in Excel (Create and Analyze)
• How to Make a Dot Plot in Excel (3 Easy Ways)
• Create an Organizational Chart in Excel (2 Suitable Ways)