Ho to do a Tornado Chart in Excel

Tornado diagrams, or tornado charts, are modified versions of bar charts and are also one of the classic tools of sensitivity analysis used by decision makers to get a quick overview of the risks involved. In other words, a tornado diagram or chart shows a financial analysis for a project.

Let’s start by transforming the above information into a data table like this:

Tornado table

The data ranges are shown in rows 4, 5, and 6.High to low, the base value column shows average expected values. A low value for an uncertainty is the value where there is only a 10% estimated chance that the realized value will be less than said value. A similarly high value for an uncertainty is the value where there is only a 10% estimated chance that the realized value will be above said value.

How to insert a Tornado Chart?

So, once you set up the data in a table like the one shown, here are the steps to construct a tornado chart:

1. Select the data, excluding the Base column

Tornado select data

2. On the Insert ribbon, choose Bar. Pick a Clustered Bar (the first one in the 2-D section).

Tornado clustered column chart

Right-click on the horizontal axis and choose Format Axis.

Tornado format axis

3. At the bottom of the Axis Options pane, look for Vertical Axis Crosses. Choose the Axis value radio button, and type your base case value (31 in our example, average of base values).

Tornado axis options

4. Without closing the window, click on any of the bars, which should bring up the Series Options pane. Adjust the sliders for Series Overlap & Gap Width accordingly.

See also  How to create a chart with grouped data?

Tornado gap width

That’s it! Now you can format the chart as you like. The final tornado chart in Excel should look like the chart shown below.

Tornado chart Excel