How to Create a Tornado Chart in Excel

An Excel Tornado Chart is useful for those who want to analyze their data for better decision-making. The best use of it is for sensitivity analysis but you can use it for comparison purposes. That’s why it is a part of our advanced charts list on Excel Champs.

What is an Excel Tornado Chart

The Excel Tornado Chart is like a two-sided bar chart (looks like a tornado) where you have two data bars that are opposite to each other and make it easy to compare both of them. As I said, it’s a useful tool for sensitivity analysis, but you can use it where you need to compare values.

According to Wikipedia:

It’s a special type of bar chart in which data is sorted vertically from highest to lowest. With this type of shape, it looks like a tornado.

How to Create a Tornado Chart in Excel

In Excel, there is no default option to create a tornado chart but you can use the default bar chart and customize it. Today, in this post, we will learn to create it.

Make sure to download this sample file from here to follow along. To create a tornado chart in Excel you need to follow the below steps:

1. First of all, you need to convert data of Store-1 into the negative value. This will help you to show data bars in different directions. For this, simply multiply it with -1 (check out this smart paste special trick, I can bet you’ll love it).
   
2. After that, insert a bar chart using this data. Go to Insert Tab ➜ Charts ➜ Bar Chart and with this, you’ll get a bar chart like below where you have two sides (one is side is for positive values and another is for negative).
   
3. From here, select the axis label and open formatting options and in the formatting options, go to axis options ➜ Labels ➜ Label Position. Change label position to “Low”.
   
4. Next, you need to change the axis position in reverse order. This will adjust bars from both of the sides and for this, go to Axis options ➜ Axis position ➜ tick mark “Category in reverse order”.
   
5. Now you need to change the series gap and gap width. This will help to streamline data bars with each other and for this go to series options -> Change series overlap to 100% and gap width to 10%.
   
6. And you need to change the number formatting of the horizontal axis. And for this, go to the Axis Options ➜ Number ➜ select custom ➜ paste following format and click add.
   
7. In the end, just like the above step, you need to change the format for data labels for Store-1 so that it doesn’t show the negative signs and for this go-to label options ➜ Number ➜ select custom ➜ paste following format and click add.

Congratulations, now you have your first tornado chart in your worksheet, just like below.

You can also create a dynamic chart range for your chart so that you don’t need to update it again and again when you need to update the data.

Create a Tornado Chart using Conditional Formatting

Unlike the way we have used the above method, you can use conditional formatting as well. To create a tornado chart with conditional formatting:

• You just need to plot your data as I have in the below snapshot. As you can see, you have data for both of the stores (store 1 and store 2) in the table.
  
• So first of all, align store 1 column to right and store 2 to left.
  
• The next thing is to create apply conditional formatting. You have to apply it to both of the columns one by one.
• From here, select the store 1 one column and go to the Home Tab ➜ Styles ➜ Conditional Formatting ➜ Data Bars ➜ More Rules.
  
• In the “More Rules” dialog box, you need to do define below things to create data bars:
  • Color: Whatever color you want.
  • Border: Solid (if you want)
  • Direction: Right to Left.
    
• After that, click OK to apply the data to the store 1 column.
• The next thing you need to do is to apply a bar to store 2 columns using the same method which you have used for the second. Simply select the column and apply data bars from the more rules, using the below settings:
  • Color: Whatever color you want
  • Border: Solid (if you want)
  • Direction: Left to Right.
    
• At this point, you have a table that has a tornado chart on which you have applied data bars with conditional formatting.
  

Use REPT Function to create an In-Cell Tornado Chart in Excel

I love to do things in a different way or try new unconventional ways to do old things. At this point, you know how to create a tornado chart with a bar chart and with conditional formatting as well. 

But, there is one more thing which we can try, that’s the REPT function (If you want to learn more about it, check from this function page). To create a TORNADO chart with REPT function you can follow the below steps:

1. First of all, you need to set up your data just like we have in the below snapshot. Here you have a column with product names and two columns for each store (one is for values and second for values).
   
2. From here we need to insert REPT function in the data bar column of the store 1 and for this insert the below formula in the first cell and drag it down to the last cell.=REPT(“|”,D3/10)
   
3. After that select the entire column and change the following things:
   • The font to “PlayBill” (with this font you can have a look of data bar).
   • Change the width of the column equivalent to the largest data bar or more.
   • Change font color to orange.
   • Change text alignment to right to left.
     
4. Next, you need to follow the same method to add data bars to the store2 column and change the following things:
   • The font to “PlayBill” (with this font you can have a look of data bar).
   • Change the width of the column equivalent to the largest data bar or more.
   • Add font color to
   • Change text alignment to the left to right.
     

Sample File

Download this sample file from here to learn more.

Conclusion

Some people call it tornado diagrams, a useful tool for decision-making by comparison. You can compare two different items or a single item for the different periods.

So that’s the entire story about tornado charts and YES, you just need 7 STEPS to create it. One thing I just forget to tell you is that you can also use conditional formatting or a REPT function to create an in-cell tornado chart.

I hope you found it useful and it will help you take your skill to next level.

Now tell me one thing. Have you ever used a tornado chart? Please share with me in the comment section, I’d love to hear from you. And please don’t forget to share this tip with your friends. I’m sure they will appreciate it.