Excel Functions Tutorials
- Excel Charts
- Types of Charts in Excel
- Area Chart in Excel
- Bubble Chart in Excel
- Chart Templates in Excel
- Change Chart Style in Excel
- Calendar Template in Excel
- Inventory Template in Excel
- Clustered Bar Chart in Excel
- Clustered Column Chart in Excel
- Column Chart in excel
- Combination Charts in Excel (Combo)
- Excel Combo Chart
- Control Charts in Excel
- Doughnut Chart in Excel
- Dynamic Chart in Excel
- Excel Chart Wizard
- Flow Chart in Excel
- Flowchart Excel Examples
- Funnel Chart in Excel
- Gantt Chart in Excel
- Gantt Chart Example
- Gauge Chart in Excel (Speedometer)
- Graphs vs Charts
- Histogram Excel Chart
- How to Make Graph / Chart in Excel?
- Legends in Excel Chart
- Line Graphs / Charts in Excel
- Line Chart Examples
- Marimekko Chart in Excel (Mekko)
- Normal Distribution Graph in Excel
- Organization Chart in Excel
- Pareto Chart in Excel
- Pie Chart in Excel
- Make Pie Chart in Excel
- Pivot Chart in Excel
- Radar Chart in Excel (Spider Chart)
- Stacked Chart in Excel (Column, Bar & 100% Stacked)
- Stacked Bar Chart in Excel
- Stacked Column Chart
- Stock Chart in Excel
- Scatter Plot in Excel (Chart)
- Tornado Chart in Excel
- Dot Plots in Excel
- 3D Plot in Excel
- 3D Scatter Plot in Excel
- Standard Deviation Graph in Excel
- S Curve in Excel
- Waterfall Chart in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)
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.
Tornado Chart in Excel
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.
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 –