How to Make a Tornado Chart in Excel for Sensitivity Analysis
Build a clean tornado chart in Excel for sensitivity analysis: diverging bars, auto-sorted by impact with dynamic arrays. No add-ins, no helper-column hacks.
How to Make a Tornado Chart in Excel for Sensitivity Analysis
A tornado chart answers the question every financial model eventually faces: which assumptions actually matter? You flex each input — price, volume, cost inflation, discount rate — between a plausible low and high case, chart the swing in your output (NPV, project cost, margin), and sort the bars so the biggest swing sits on top. The result funnels to a point like a tornado, and it tells you in one glance where to spend your diligence effort.
Excel has no native tornado chart type, but unlike some charts that fight you the whole way, this one is a clean ten-minute build from a standard bar chart. Most tutorials still teach it with clunky helper columns and manual re-sorting. Here’s the modern build — including a dynamic-array trick that keeps the bars sorted by impact automatically.
If you’re not sure a tornado is the right shape for your data, our chart decision guide covers when diverging bars beat the alternatives.
Step 1: Run the Sensitivity Cases
A tornado chart displays results you’ve already computed. For each input variable, hold everything else at base case and record the output at the variable’s low and high value. Structure it like this:
| Variable | Output at Low | Output at High |
|---|---|---|
| Unit price | -1.8 | 4.6 |
| Sales volume | -0.9 | 3.7 |
| Raw material cost | 0.4 | 2.5 |
| Discount rate | 0.8 | 2.1 |
| Launch delay | 1.0 | 1.9 |
Values here are NPV in $ millions; base case is 1.4. Two rules that separate a credible tornado from a decorative one: use plausible extremes (P10/P90 estimates or historically observed ranges), not lazy ±10% on everything; and flex one variable at a time — a tornado shows single-factor sensitivity, nothing more.
Step 2: Convert Values to Swings Around the Base Case
The chart plots each bar as a deviation from base case, so downside runs left and upside runs right. Add two columns:
- Low swing:
=B2-$F$1(output at low minus base case) - High swing:
=C2-$F$1
where F1 holds the base case. Unit price becomes −3.2 / +3.2; launch delay becomes −0.4 / +0.5.
Step 3: Sort by Impact — the Modern Way
The tornado shape depends entirely on sorting: largest total swing at top. The old method is sorting the table by hand and re-sorting every time the model changes. The modern method is one formula. In a fresh range, spill the whole chart source, sorted by total swing:
=SORTBY(A2:E7, ABS(D2:D7)+ABS(E2:E7), -1)That’s the entire chart source, permanently ordered by impact. Update the model, and the ordering — and the chart — reorders itself. (On Excel 2019 or older without dynamic arrays, sort the table manually by a total-swing column, descending. It works; you just have to remember to redo it.)
Step 4: Insert the Diverging Bar Chart
- Select the variable names plus the two swing columns from the sorted range.
- Insert > Charts > 2-D Bar > Clustered Bar.
- Right-click either series > Format Data Series > set Series Overlap to 100% and Gap Width to about 40%. Overlapping the series is what merges each pair into one continuous left-right bar.
Because your largest swing is in the first data row, Excel will draw it at the bottom — bar charts plot in reverse. Fix it: right-click the vertical axis > Format Axis > check Categories in reverse order, and under Labels set Label Position to Low so variable names don’t collide with the leftward bars.
Step 5: Format Like an Analyst, Not a Default
Three finishing moves take this from “Excel output” to “board deck”:
- Hide the minus signs. Select the horizontal axis > Format Axis > Number > Custom > enter
0.0;0.0. Downside magnitudes now read as magnitudes, which is how people think about them. - Color by direction, once. Downside series in a muted red, upside in a muted blue or green. Skip per-bar rainbow coloring — the sort order already encodes the ranking.
- Mark the base case. The vertical axis line at zero is your base case; give it a heavier line weight and label it in the title or a text box (“Base case NPV: $1.4M”). A tornado without a stated base case is just decoration.
Delete gridlines and the legend if the colors are labeled directly. Title with the finding: “NPV is a pricing story — nothing else comes close.”
Reading the Chart Honestly
The top bar is where your model lives or dies, and it deserves most of your validation effort. But two honest caveats belong in any writeup that includes a tornado. Asymmetric bars matter as much as long ones — a variable with modest upside and severe downside is a risk flag even if its total swing ranks third. And one-at-a-time flexing ignores interactions; if price and volume move together in your market, the tornado understates combined risk. Say so in the footnote rather than letting the chart imply more certainty than it has.
Tornado charts pair naturally with variance reporting — once you know which assumptions drive the plan, tracking them is the follow-up. Our budget vs actual variance chart template picks up exactly there.
FAQ
Is a tornado chart the same as a butterfly chart? Same construction, different job. A butterfly (or population pyramid) compares two groups across categories; a tornado shows output sensitivity to input ranges, sorted by impact. The sorting is what makes it a tornado.
Why are my bars not merging into single rows? Series Overlap isn’t at 100%. Right-click a series > Format Data Series > drag Series Overlap to 100.
My chart didn’t update after I changed the model — why? If you built from the SORTBY spill range, check the chart’s data reference still covers the spill and that calculation is set to Automatic. Our guide to Excel charts that won’t update walks through the seven usual causes.
Can I build a tornado in Google Sheets? Yes, with the same diverging-bar approach, though Sheets lacks the reverse-category-order toggle, so sort your data ascending by impact instead.