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

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.

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.

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

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

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

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

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

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

## 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 creating a butterfly chart in Excel with 2 easy methods. Let us know your feedback on this.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF