Feb1
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:
- Sort your data descending on the Delta column (which is the difference between High and Low columns).
- Select the data without the Delta column. Create a chart. Choose Bar type -> Clustered bar. Finish.
- 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:”
- Double click the vertical axis. In the Scale tab tick “categories in reverse order”. In the Patterns tab, set “Tick mark labels” to low.
- 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.
No related posts.
Brilliant!! Thanks SO much!
Thank you, great tip & trick.
Thank you! Short and simple
THANK YOU! Easy steps to handle negative values.
It's perfect! Nice and easy. We would need more people like you in this world…
Yay. I needed to whip on up quick on old data and this worked like a charm!
Dont worry I've worked it out by putting in a second x axis, Thanks anyway
Is it possible to compare two tornadoes in the one graph, it seems that you can only have 1 “Category (X) axis crosses at:”
Thanks for this powerful tool
@Joe – Thanks for your note – you brought to my attention that the images were unavailable. They are fixed now
Juan C.
Fantastic! Thanks so much.
[...] is an update to my previous post on tornado [...]
How about an update to the latest MS Office version?
@Shaun – thanks for pointing it out. Look at the new post http://8020world.com/jcmendez/2009/05/business/tools-methodologies/tornado-charts-in-excel-2007-update/
I am looking for a easy, straight forward way to create a tornado diagram without an addin…I see you have an example, but the examples are, for some reason, not available. is there a way to see this example? it would help to understand your capabilities.
Thanks
Joe
@Joe – Thanks for your note – you brought to my attention that the images were unavailable. They are fixed now
Juan C.
Muchas Gracias!!! The tick mark labels thing is a great tip.
god bless you juan carlos mendez garcia
You are a lifesaver and an Excel genius. Thank you!
OMG!!! That was so easy. Thank you, thank you, thank you. You save me hundreds of dollars and hours of time!
Thank you for the tips, I was able to recreate it! However, I can not figure out how to get the category labels over to the left side like you do. Mine keep coming up on top of the bar graphs. How do you move them to the far left?
Nice and straightforward…thanks!
[...] are considered versus when they are not. What are the assumptions that most impact the results? A tornado diagram, as discussed in a previous entry, may provide a good way to show the sensitivity to the [...]