How to Create a Tornado Chart in Excel

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 which are opposite to each other and makes 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:

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

tornado chart using bar chart

Create a Tornado Chart using Conditional Formatting

Unlike the way we have used in 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.
    1-date-to-create-a-tornado-chart-in-excel-using-conditional-formatting
  • So first of all, align store 1 column to right and store 2 to left.
    2-change-text-alignment
  • 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.
    3- go-to-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.

    4-add-option-for-data-bar-store-1
  • 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.

    5-add-option-for-data-bar-store-2
  • At this point, you have a table that has a tornado chart on which you have applied data bars with conditional formatting.
    6-final-tornado-chart-with-cf

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

I love to do things in a different way or try new un-conventional 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 one more thing which we can try, that’s REPT function (If you want to learn more about, check from this function page). 

To create a TORNADO chart with REPT function you can follow the below steps:

  • 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).
    1-data-touse-rept-function-for-tornado-chart
  • 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)
    2-apply-rept-function-store-one-column
  • 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.

    3-change-font-alignment-color
  • 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.

    4-final-chart-with-rept-function

Sample File

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 chart and YES, you just need 7 STEPS to create it.

One thing I just forget to tell you 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.


About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

13 thoughts

Leave a Comment

Your email address will not be published.

  1. HI,

    How did we get positive values for Store 1 in the chart number area left hand side. As we converted to negative values the same negative values are displaying in the chart too

    Reply
  2. Excelente, diseño bastante fácil para desarrollar, en cuanto a su presentación nos detalla un panorama mas claro de existencias en almacenes

    Reply
  3. Funnel Chart and Tornado Chart are synonymous. However, the chart you have given above is a variation of tornado chart for comparing two data sets. Hence, i think that it should be named a bit differently to highlight its usage.

    Reply
      • @Oz
        Yes, you are right.
        @Ramana
        There is a basic difference between Funnel chart and tornado chart. A stage in a funnel chart can’t be larger than the previous one. Tornado chart works on two different ways of comparison.

        Reply