How to Create Tornado / Funnel Chart in Excel

Tornado or funnel chart is basically a bar chart of decendingly sorted data. It looks like a funnel or tornado, from which it gets its name.

In this chart, one group's data is shown on the left side of the axis and other group's data is shown on the right side of the axis. This makes it easy to compare these groups.

There are many ways to create a tornado or funnel chart in excel, but in this article, we will discuss two easiest methods of creating the funnel chart.

Create a Tornado Chart in Excel using Excel Stacked Bar Chart

Create Tornado Chart in Excel using Conditional Formatting

Let's start with the Bar Chart method.

Create a Tornado Chart in Excel using Excel Stacked Bar Chart

Here I have this sample data.

In A2 to A11, we have year. In B2:B11 we have Group A's record and in C2:C11 we have B's record. It is already sorted Ascendingly by year.

1. As we know, the minus values are portrayed on the opposite side of the axis. We will use this to our advantage. Convert all values of group A into - (negative) values. You can do this by multiplying by -1.

1. Now select the data? go to insert --> charts --> Column or Bar Charts --> Stacked Bar Chart. Click on it.

1. We have our chart but does not look good.

Correct the Y-axis: Right-click on the y-axis. Click on the format axis.
In labels, click on label position drop-down and select low.

Now the chart looks like this.

Select any bar and go to formatting. Reduce the gap width to zero and put borders as white.
Let's remove the minus sign (-) from the chart. Select the x-axis. Go to formatting. In the axis option, click on the number. In category, select custom. In format code write this ###0,###0. Click on add.
This is almost ready but it more looks like a mountain than a funnel or tornado. If we can invert it, it will look like a funnel chart. To do so, click on the y-axis and go to the axis option. In the Axis option, check Categories in reverse order.

And it is ready. With a little bit of beautification or according to your theme, you can create this kind of chart.

The above chart is easy to understand and interpret. This was the excel chart method for creating a tornado chart. Let's look at the conditional formatting method.

Create Tornado Chart in Excel using Conditional Formatting

To create a tornado chart using conditional formatting, we will use the same data. We will convert group A's values into negative numbers.
Next, we will sort the table smallest to largest with respect to group A's data.

Finally, data will look like this.

Now follow these steps to create a tornado chart in excel.

1. Select group A's Data.
2. Go to Home --> Conditional Formatting --> Data Bars. Click on any style.

For negative numbers, the default color is red and the direction of data bars is right to left.

1. Next, do the same for group B. Choose any color that you like. Align text to left.

1. Now we need to get rid of that - a sign from group A's data. To do so, select group A's data. Press CTRL+1 to open number formating. Go to custom, and write this ###0;###0 code .

Select A2:B11 and press CTRL+SHIFT+&. A border will be added immediately.
Fill the background with an appropriate color you like. Finally, you'll have this tornado chart ready.

So the chart is ready. As I told you the red color for negative numbers is the default. But we don't have negative numbers actually. We just converted them into negative numbers so that the direction of bars is apposite.
So, how do we change the color of group A's Bars? Follow these steps:

1. Select group A's Data. Go to conditional formating --> Data bars --> More Rules. New formatting rule dialog will open.
2. Click on the negative values and axis button.

From fill color, choose the color you want.

And it is done. The color of the bar is changed as we wanted.

With a little bit of formatting, it can look like this.

Tune the chart: The problem with the above chart is that group A and B's bars are not relatable. In the group, A maximum value is 88 (or say -88 as a minimum) and all other bars size is created in reference to that. In Group B, the maximum value 97 and its bars are created in reference to that. Hence the bars are not comparable.
To make them comparable, we need to have the largest value for both groups of the same magnitude. So if we add -100 for A and 100 for B to and then use data bars, they will become comparable.
To do so, follow these steps.

1. Insert A row in the table.

1. In A group write -100 and in B write 100.

Now they comparable. Every data bar is compared to 100. Hide this new row.

The Excel Tornado chart is ready. There are many other ways of creating a tornado chart. But these were my favorite and I wanted to share it with. Do you guys have any special chart types? Let me know in the comments section below. If you have any doubts about any excel VBA topic, ask that too.