Tornado Chart in Excel (Easy Learning Guide)

A tornado chart in Excel is a useful tool for sensitivity analysis and comparison. As it greatly helps an efficient comparison between two variables, it is commonly used by businesses and professionals for decision-making purposes.

The general outlook of a tornado chart includes horizontal bars placed opposite to each other with the base as the middle of the chart. As it resembles the shape of a butterfly, it is also known as a Butterfly Chart or a Funnel Chart.

Putting together a tornado chart in Excel often gets a little tricky. Here is all that you need to know about a Tornado Chart in Excel.

How to Create a Tornado Chart in Excel?

Unlike other simple charts, there exists no option to create a Tornado Chart in Excel. Users, therefore, need to create a default bar chart and customize it to a tornado chart.

Follow the steps below to learn how.

Step 1:

Below is a dataset that contains two variables i.e. sales of the same product in two different regions.

Dataset containing two variables

First, you need to arrange the first variable (i.e. California for the given dataset) in ascending order. Right-click on the first variable, and from the pop-up menu, choose Sort > Custom Sort as shown below.

 Sorting the data

From the dialogue box that then opens, choose to Expand the Selection.

Applying the sorting settings to all the data

As we need to sort the values based on the first variable i.e. California, we have selected the same as ‘Sort By’. The Order is to be kept from smallest to Largest as we need values to be arranged in ascending order.

Sorting settings

Here is how excel has arranged the values under the Column Header ‘California’ in ascending order, and the remaining data is accordingly arranged.

First variable arranged in an ascending order

Step 2:

Select the data to be plotted and insert a stacked bar chart into excel as follows.

Insert > Charts > Insert Column or Bar Chart > 2-D Bar

Inserting a stacked bar chart

Step 3:

Once the chart is inserted, select the first variable that is plotted on the graph. California in our given example is presented by blue so we selected blue bars. Right-click to see the pop-up menu and choose ‘Format Axis’ or ‘Format Data Series’.

Option to Format Data Series

From the Plot Series options, check ‘Secondary Axis’ as shown below.

Formatting axis to add secondary axis

Excel would add a secondary axis to the top of the graph.

Step 4:

Right-click the secondary axis plotted on top and choose the option ‘Format Axis’ as shown below.

Formatting the secondary axis

Within the secondary axis formatting options, two changes are to be made.

  1. Change the minimum axis bounds to negative $90,000. Why that? The highest number of our dataset comes off to be $76,800 – as the axes are plotted at an interval of 20,000, it must fall somewhere after $70,000.  The highest value next to $70,000 at an interval of 20,000 is $90,000. Just pair that value with a negative sign and input it as the minimum bounds. The maximum bounds value would also be the same number but only with a positive sign.
  1. Under Display Units, check the box for ‘Values in reverse order’.
Setting the maximum and minimum bounds for the secondary axis

Step 5:

This must-have loused up your graph with the orange and blue data plots intermingled, making little sense. This is because the primary and secondary axes are not in line.

To align both axes, select and right-click the primary axis to format the axis as follows.

Setting the maximum and minimum bounds for the primary axis

Again the minimum and maximum bounds are set to -$90,000 and $90,000, respectively. However, for the primary axis, we will leave the box for ‘Values in reverse order’ unchecked.

Step 6:

The graph now somehow seems structured. However, there are yet a few steps more to follow. As we no longer need the secondary axis now, it’s time we say goodbye.

Right-click on the secondary axes to delete it as follows.

Deleting the secondary axis

Step 7:

Next, we do not need the numbers on our chart to be negative. There’s nothing negative in our dataset, after all. To change these numbers to positive, select the primary axis, right-click and choose ‘Format axis’.

Under the Format Axis menu, open the Number drop-down and make the following changes:

  1. Category : Choose Custom
  2. Type : Choose ###0;###0
  3. Format Code : Choose ###0;###0
Changes to be made to the primary axis

Viola! The primary axis now presents a positive number line on both sides.

Positive numbers on the primary axis

Step 8:

As of now, the product labels overlap with the bar chart and are barely visible. To make them prominent, select the chart (the blue and orange bars), right-click and click Format Axis.

Under Format Axis, open the dropdown menu for Labels. Set the label position as ‘Low’ to make the product labels move to the left side as shown below.

Positioning the product labels

Feels a little dull? Select the product labels and opt for the Bold option from Home > Font. You may also give them different colors, styles, and fonts.

Step 9:

This step is all about making your chart more appealing and eye-catching. It may be hard for users to identify what figure does each bar represent.

You may add the value represented by each bar parallel to it simply by adding data labels to it. For instance, to add the sales represented by the blue bars, select either of them, right-click and from the pop-up menu, select ‘Add Data Labels’.

Adding data labels to the Tornado Chart in Excel

Here is how excel adds data labels to the chart.

Data labels added to the Tornado Chart

You may reposition these labels from the center of each bar to the side or even against the end of each bar. Similarly, you may change their font color, size, and style to add prominence to it.

Step 10:

Time to do some final touches to our almost-ready Tornado Chart. Name your chart with a suitable title by clicking and typing in the header ‘Chart Title’.

Title added to the Tornado Chart

If the chart seems a little too skinny and suppressed, pump it up a little by increasing the size of each bar. Right-click the chart to open the ‘Format Data Series’ menu.

Under the ‘Series Options’, reduce the Gap Width to your desired size. Must note how this works opposite i.e. decreasing the gap width increases the width of the bars and vice versa.

Increasing the size of bars

This is it! You have your Tornado Chart all set to go.

There are many more editing options that you can exercise to make your Tornado Chart in Excel more and more appealing. For instance, you may change the color of bars from solid fill to gradient fill, add borders to them, and much more.


Creating tornado charts requires a great deal of effort, especially when compared to other in-built charts of Excel. However, the easy-going and efficient results make the effort worth it. It will take you a round or two of practice to master the art of pulling together a Tornado Chart in Excel.

Once you know the steps, surprise people with appealing tornado charts that are the best for two-item comparisons and sensitivity analysis.

Suggested Tutorial: Square Root in Excel

Leave a Comment