How to Make Butterfly Chart

A butterfly chart, which is also known as a tornado chart, is a type of chart used to visualize the changes in the difference between two variables over time. It is called a butterfly chart because it resembles the shape of a butterfly, with two “wings” representing the changes in the two variables. This chart is particularly useful for visualizing and comparing changes in the spread between two data sets.

Here is a step-by-step guide on how to make a butterfly chart in Microsoft Excel:

Prepare your data: Create a table that contains two columns of data, one for each variable you want to compare. Make sure the data is organized chronologically, with the earliest data first and the latest data last.


How to create the Butterfly Chart?

Click on any part of the data (1), insert (2), and bar chart (3), then stacked bar (4).

insert stacked bar

Click on Design.

ribbon design

Click on Add chart element (1), Legends (2), and Top (3).

add legend top

Right click on one of the series (1) and Select Data (2).

right click select data

Click on the arrows to sort the series Padding South, South, Gap, North, and Padding North (1), and press ok (2).

sort series

Right-click on padding North (1), and click on no fill (2).

Right click padding North

Click on no fill.

no fill

Note: You should repeat previous steps on Gap, and Padding north series.

Right-click on the gap (1). Add data labels twice (2 & 3).

butterfly chart add data labels

Right-click on gap (1), and choose Format Data Labels (2).

butterfly chart format data labels

Check category name (1), and uncheck values (2).

See also  How to Create a Waterfall Chart in Excel

butterfly chart category name value uncheck

Right-click on the x axis (1), and choose format axis (2).

butterfly chart format axis

Click labels (1), and choose “None” in the labels position (2).

butterfly chart labels position

Click on the line, and press delete.

butterfly chart delete line

Click on Padding North, Gap, and Padding South, and then press delete.

butterfly chart delete padding

Stretch the legends so both the south and North is showing on their sides.

butterfly chart stretch legend

In conclusion, you can keep working on the chart as you please.

butterfly chart example

A butterfly chart is a useful tool for visualizing and comparing the changes in the difference between two variables over time. By following the steps outlined in this guide, you can create a butterfly chart in Microsoft Excel that clearly displays the changes in the two variables and makes it easy to compare and understand the data. Whether you are a business analyst, financial analyst, or data analyst, a butterfly chart can help you to quickly and easily analyze and communicate the changes in your data.

You can download free Butterfly Chart template here.