Easy creation of tornado charts in Excel - 5 steps, no add-ins

Tornado diagrams are a classic tool of sensitivity analysis for decision analysis. They are not used as frequently as you would expect, given how clearly they help showing the impact of different variables on a geven outcome. As suggested by Ted Eschenbach on a recent article of Engineering Economist, (issue of 06/22/2006), perhaps this is due to difficulties in constructing them.

Sensitivity analysis is needed to address the inherent uncertainty in engineering economy applications because (1) time horizons are measured in years or decades and (2) much economic analysis is done at the feasibility and preliminary design stages. This is often shown using relative sensitivity analysis charts or spiderplots, which have a long and rich history in practice and texts (they are described in 10 of 18 texts reviewed, including Blank and Tarquin (2002), Canada et al. (1996), Eschenbach (2003), Lang and Merino (1993), Park (2002, 2004), Sullivan et al. (2003), Thuesen and Fabrycky (2001), White et al. (1998), Young (1993). Tornado diagrams are not new, but they have not been used nearly as frequently. Only one of the 18 texts included a tornado diagram (Eschenbach, 2003)–

Searching Google on how to make tornado charts, you’ll get many results, most of them requiring you to download an add-in. Keep reading to see how you can create tornado charts with plain Excel in just 5 steps… very easy and straightforward!!

This is a tornado chart created with the method described below:

Here is how to make a tornado chart in Excel, no extra add ins, in a few simple steps:

Lay out the data as follows:

1. Sort your data descending on the Delta column (which is the difference between High and Low columns).
2. Select the data without the Delta column. Create a chart. Choose Bar type -> Clustered bar. Finish.
3. Double click the horizontal axis. In the Scale tab type in your base case value (30 in the example) at “Category (X) axis crosses at:”
4. Double click the vertical axis. In the Scale tab tick “categories in reverse order”. In the Patterns tab, set “Tick mark labels” to low.
5. Double click a bar to edit the series format properties. On the Options tab, choose 100 overlap.

Done!!! Now, remove all the clutter that the default Excel charts will have, beautify and enjoy.

Related posts:

1. Column - Stack combination chart in Excel Not very common, but sometimes useful, this chart is made...
2. Market partition - Mekko chart in Excel, no add-ins Mekko charts are two dimensional graphs that analyze how data...
3. Normalize Excel tables Business data is quite often expressed across many dimensions. The...
4. Yet another in-cell Excel bar chart technique Two improvements over the technique described by Juice Analytics and...
5. Modeling market adoption in Excel with a simplified s-curve UPDATE 10/31/2007: Here is a screencast for this model Often...