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