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

UPDATE: A newer post is available, with Excel 2007 instructions and a screencast

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 (7 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. Tornado Charts in Excel 2007 Update This is an update to my previous post on tornado...
2. The Devil’s guide to spreadsheet creation From http://www.sysmod.com/praxis/prax0404.htm#Excel The Devil’s guide to spreadsheet creation Just do...
3. Extreme Makeover: Chart Edition – July 19 As every week I end up receiving at lest one...