Tornado Chart in Excel

Tornado chart in excel is a type of bar chart which is also used to compare data among different types of data or categories, the bars in the tornado chart are horizontal and this chart is basically used to show the impact such as how a condition will impact the result on the outcome.

Excel Tornado chart helps in analyzing the data and decision making process. It is very helpful for sensitivity analysis which shows the behavior of the dependent variables i.e., it shows how one variable is impacted by the other. In other words, it shows how the output will be affected by the input.

This chart is useful in showing the comparison of data between two variables.

It is a Bar chart having the bars represented horizontally. This chart has the bars of two variables facing opposite directions with the base for the both in the middle of the chart which makes it look like a Tornado and so it is named as Tornado Chart. It is also called a Butterfly chart or Funnel Chart in Excel.

Examples of Tornado chart in excel

Now let us learn how to make a Tornado chart in excel. The below example shows the comparison of data for the products in two different locations.

You can download this Tornado chart excel Template here – Tornado chart excel Template

Example #1 – Comparison of Two Variables

Enter the data set in an excel worksheet with the variables name and values.

Arrange the data set of the first variable in ascending order by sorting it Smallest to Largest
Select the data to insert a chart (A1:C7)

Select the 2-D Stacked Bar Graph from the Charts section in the Insert tab.

Select the first variable and do right click to select “Format Data Series” option
Select the “Secondary Axis” option in the Format Data series panel

Select the Secondary Axis in the chart and do right click to select the “Format Axis” option

Set the Axis Bounds minimum value under Axis options with the negative value of the maximum number (Both Maximum and Minimum bounds should be same but the minimum value should be negative and Maximum value should be positive)

Also, check the box for “Values in Reverse Order” under Axis options in Format Axis panel.

Now select the primary axis and do right click to select the “Format Axis” option

Set the Axis bounds minimum with negative and maximum with positive values (same as above)

Now click on the axis showing the product name (A, B, C…).

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects) 4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Select and do right click to select the “Format Axis” option

Select the Label Position as “Low” from the drop-down option in Labels section under Axis options

This is how your chart looks now. Do right click on the Bars to add value labels by selecting the “Add data labels” option. Select the “Inside Base” option to make the labels show up at the end of the bars and delete the gridlines of the chart by selecting the lines.

Select the Primary Axis and delete it. Change the chart title as you like.

Now, your Excel Tornado chart is ready.

Example #2 – Excel Tornado Chart (Butterfly Chart)

The excel tornado chart is also known as the Butterfly chart. This example shows you how to make the chart look like a butterfly chart.

Create a data set in the excel sheet with the product name and the values
Just add another column in the data set with the column name as “GAP” after the variables column
In the “GAP” column add the number as 1000 for all the products

Create a graph with this data by including the GAP column
Follow all the above steps to create a graph
Make sure the GAP bars are in Primary axis

Right click on the chart area and select the “Select Data” option
Under Legend Series, move the “GAP” column to the middle

Right click on the bars and select “Format Data Series” option

Select No Fill and No Line options under Fill and Border sections

Double click on the legend “GAP” and hit delete to remove the column name from the chart

Now the chart looks like a Butterfly and you can rename it as Butterfly chart.

Example #3 – Sensitivity Analysis

Sensitivity analysis shows how the variation in the input will impact an output. To build a tornado chart in excel for sensitivity analysis, we need to set data for two variables. One variable with a negative value and another one with the positive value

The below example shows how the increase or decrease in the Cost is impacting the margin.

Follow the same steps shown in the previous examples to build the chart.

In this example, I’ve shown the cost in a negative value and the margin in a positive value.

In the above chart, you can see the impact of Cost on Margin. Excel tornado chart shows that the increase in the Cost is decreasing the margin and decrease in cost is increasing the margin.

Things to Remember

To build a Tornado chart in excel, we need data for two variables to show the comparison
The data should be sorted to make a chart look like a Tornado so as to make the highest value come on the top.
The excel Tornado chart is dynamic as it gets updated with the changes made in the values of variables in the data set.
Tornado chart in excel is not useful in showing the values of the independent variables.

Excel

Financial Functions Excel

Lookup Functions Excel

Excel Charts

Others

Certification Courses

Excel VBA All in One Bundle

Excel Data Analysis Course

VBA Macros Course

Others