Tornado Chart in Excel

Tornado Chart in Excel

Tornado chart is one of the types in Bar Graph/Chart. Tornado chart in Excel is an effective tool for visualizing the data through graphical representation. 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.

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…).

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

Recommended Articles

This has been a guide to Tornado Chart in Excel. Here we learn how to create Excel Tornado chart along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

• What is the Control Chart?
• How to Change Case using Excel Functions?
• Running Total with Name Range in Excel
• Excel Bullet Points | Insert using Excel Formula
• How to use PI in Excel?
• Create Gauge Chart
• Flowchart Excel Examples
• Create Pie Chart in Excel
• Examples of Line Chart