Download FREE Tornado Chart Excel Template and Follow Along!
Tornado Chart Excel Template.xlsx

Table Of Contents

arrow

Tornado Chart in Excel

Publication Date :

Blog Author :

Edited by :

Reviewed by :

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.

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.


    Example 1-3

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


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


    Example 1-5

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


    Example 1-6

    Example 1-8

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


    Example 1-9

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


    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.


    Example 1-11

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


    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.


    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
  • Create a graph in excel with this data by including the GAP column.
  • 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 analysis. 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.