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

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Create Butterfly Chart in Excel


How to Create Butterfly Chart in Excel: 2 Easy Methods

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

How to Create Butterfly Chart in Excel


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 and 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.

Create Excel Butterfly Chart with Padding Columns

  • Next, insert this formula in cell C5.
=11500-D5

Create Excel Butterfly Chart with Padding Columns

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.

Create Excel Butterfly Chart with Padding Columns

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.
=11500-F5

Create Excel Butterfly Chart with Padding Columns

  • 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.

Create Excel Butterfly Chart with Padding Columns

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

Create Excel Butterfly Chart with Padding Columns

  • 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.

Create Excel Butterfly Chart with Padding Columns

  • 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.

Create Excel Butterfly Chart with Padding Columns

Create Excel Butterfly Chart with Padding Columns

  • 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.

Create Excel Butterfly Chart with Padding Columns

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

How to Create Butterfly Chart in Excel

Read More: How to Make Floating Bar Chart in Excel


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.

Make Butterfly Chart by Formatting Axis in Excel

  • Here, select the Clustered Bar.

Make Butterfly Chart by Formatting Axis in Excel

  • 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.

Make Butterfly Chart by Formatting Axis in Excel

  • Then, enable the Secondary Axis option.

Make Butterfly Chart by Formatting Axis in Excel

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

Make Butterfly Chart by Formatting Axis in Excel

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

Make Butterfly Chart by Formatting Axis in Excel

  • 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.

Make Butterfly Chart by Formatting Axis in Excel

  • Now, set the Label Position to Low.

Make Butterfly Chart by Formatting Axis in Excel

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

Make Butterfly Chart by Formatting Axis in Excel

  • Finally, we have our required butterfly chart.

Read More: How to Create Overlapping Bar Chart in Excel


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.

How to Create In-Cell Butterfly Chart in Excel

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

How to Create In-Cell Butterfly Chart in Excel

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

How to Create In-Cell Butterfly Chart in Excel

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

How to Create In-Cell Butterfly Chart in Excel

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

How to Create In-Cell Butterfly Chart in Excel

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

How to Create In-Cell Butterfly Chart in Excel

  • 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 Create a Radial Bar Chart in Excel


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.

Download Practice Workbook

Download the sample file and practice by yourself.


Conclusion

I hope it was an efficient article for you on creating a butterfly chart in Excel with 2 easy methods. Let us know your feedback on this.


Related Articles


<< Go Back to Create a Bar Chart in Excel | Excel Bar Chart | Excel Charts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo