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.
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.
From the dialogue box that then opens, choose to Expand the Selection.
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.
Here is how excel has arranged the values under the Column Header ‘California’ in ascending order, and the remaining data is accordingly arranged.
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
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’.
From the Plot Series options, check ‘Secondary Axis’ as shown below.
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.
Within the secondary axis formatting options, two changes are to be made.
- 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.
- Under Display Units, check the box for ‘Values in reverse order’.
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.
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.
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:
- Category : Choose Custom
- Type : Choose ###0;###0
- Format Code : Choose ###0;###0
Viola! The primary axis now presents a positive number line on both sides.
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.
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’.
Here is how excel adds data labels to the 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’.
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.
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.
Conclusion:
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