Tornado Chart in Excel

Updated on January 2, 2024
Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

A Tornado chart in Excel is a bar chart used to compare data among different data types or categories. The bars in the Tornado chart are horizontal. This chart shows the impact, such as how a condition will impact the outcome.

Tornado Chart in Excel

Excel Tornado chart helps in analyzing the data and decision-making process. It is very helpful for sensitivity analysisSensitivity AnalysisSensitivity analysis is a type of analysis that is based on what-if analysis, which examines how independent factors influence the dependent aspect and predicts the outcome when an analysis is performed under certain conditions.read more, which shows the behavior of the dependent variables, i.e., how one variable is impacted by the other. In other words, it shows how the input will affect the output.

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 both in the middle of the chart, making it look like a Tornado. So, it is named the Tornado chart. It is also called a Butterfly chart or Funnel Chart in ExcelFunnel Chart In ExcelFunnel charts in excel is similar to its name associated with it, it is used to represent data behavior in every stage defined and as the values go on decreasing thus making the shape of funnel for the chart and so the name of funnel chart, this feature of funnel chart is only available in Microsoft office 2019 or in the latest versions.read more.

Tornado Chart in Excel

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Tornado Chart in Excel (wallstreetmojo.com)

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples of Tornado chart in excel

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

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

Example #1 – Comparison of Two Variables

Let us start.

  1. Enter the data set in an Excel worksheet with the variable’s name and values.

    Tornado Chart Example 1

    Arrange the data set of the first variable in ascending order by sorting it from “Smallest to Largest.”

  2. Select the data to insert a chart (A1:C7)

    Tornado Chart Example 1-1

  3. Select the “2-D Stacked” bar graph from the “Charts” section in the “Insert” tab.

    Tornado Chart Example 1-2

  4. Select the first variable and right-click to select the “Format Data Series” option. Next, select the “Secondary Axis” option in the “Format Data Series” panel.

    Tornado Chart Example 1-3

  5. Select the “Secondary Axis” in the Excel chart and right-click to select the “Format Axis” option.

    Tornado Chart Example 1-4

  6. Set the axis “Bounds” minimum value under “Axis Options” with the negative value of the maximum number (Both maximum and minimum bounds should be the same, but the minimum value should be negative, and the maximum value should be positive).

    Tornado Chart Example 1-5

  7. Also, check the “Values in reverse order” box under the “Axis Options” in the “Format Axis” panel.

    Tornado Chart Example 1-6

  8. Now, select the “Primary Axis” and right-click to choose the “Format Axis” option.

    Tornado Chart Example 1-9

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

    Tornado Chart Example 1-10

  10. . Click the axis showing the product name (A, B, C,…). Next, select and right-click to choose the “Format Axis” option.

    Tornado Chart Example 1-11

  11. Select the “Label Position” as “Low” from the dropdown option in the “Labels” section under the “Axis” options.

    Tornado Chart Example 1-12

    That is how your chart looks now. Next, right-click the bars to add value labels by selecting the “Add data labels” option. Next, select the “Inside Base” option to make the labels appear at the end of the bars and delete the gridlines of the chart by selecting the lines.

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

    Tornado Chart Example 1-13

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 how to make the chart look like a butterfly.

  • Create a data set in the Excel sheet with the product name and the values
  • Add another column in the data set with the column name “GAP” after the variables column.
  • In the “GAP” column, add 1,000 for all the products.
Tornado Chart in excel Example 2
  • Follow all the above steps to create a graph.
  • Make sure the GAP bars are on the “Primary  Axis.”
Tornado Chart in excel Example 2-1
  • Right-click on the chart and select the “Select Data Source” option.
  • Under “Legend Entries (Series),” move the “GAP” column to the middle.
Tornado Chart in excel Example 2-2
  • Right-click on the bars and select the “Format Data Series” option.
Tornado Chart in excel Example 2-3
  • Select “No fill” and “No line” options under the “Fill” and “Border” sections
Tornado Chart in excel Example 2-4
  • Double click on the legend “GAP” and press the “Delete” to remove the column name from the chart.
Tornado Chart in excel Example 2-5

Now, the chart looks like a butterfly. So, you can rename it a Butterfly chart.

Tornado Chart in excel Example 2-7

Example #3 – Sensitivity Analysis

Sensitivity analysis shows how the variation in the input will impact an output. We need to set data for two variables to build a Tornado chart in excel for sensitivity analysisExcel For Sensitivity AnalysisSensitivity analysis in excel helps us study the uncertainty in the output of the model with the changes in the input variables. It primarily does stress testing of our modeled assumptions and leads to value-added insights. In the context of DCF valuation, Sensitivity Analysis in excel is especially useful in finance for modeling share price or valuation sensitivity to assumptions like growth rates or cost of capital.read more. One variable with a negative value and another one with a positive value.

The example below shows how the increase or decrease in the cost impacts the margin.

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

In this example, we have shown the cost of a negative value and the margin in a positive value.

Sensitivity Analysis Chart Example 3

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

Sensitivity Analysis Chart Example 3-1

Things to Remember

  • To build a Tornado chart in Excel, we need data for two variables to show the comparison.
  • We should sort the data to make a chart look like a Tornado to create the highest value 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.
  • A Tornado chart in Excel is not useful in showing the values of the independent variables.

Recommended Articles

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

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *