Introduction

Creating tornado charts in Excel can be a powerful way to visualize and compare data, especially when dealing with sensitive or critical variables. This guide will walk you through the process of building a tornado chart, providing you with the skills to present your data effectively and make informed decisions.

Understanding Tornado Charts

A tornado chart, also known as a tornado diagram or tornado plot, is a visual representation of the impact of different variables on a particular outcome or goal. It is a type of bar chart where the variables are listed vertically, with the longest bars representing the variables that have the most significant impact on the outcome.

This chart gets its name from the visual resemblance to a tornado, with the longest bars appearing to “twist” or “spiral” around the central axis. Tornado charts are particularly useful for sensitivity analysis, helping analysts and decision-makers understand which factors have the most influence on a specific result.

Step-by-Step Guide to Creating a Tornado Chart in Excel

Step 1: Prepare Your Data

Before you begin, ensure your data is organized and structured correctly. A tornado chart typically requires two columns of data: one for the variables and another for the impact or effect of each variable.

• Variables Column: This column should contain the factors or variables you want to analyze and compare. Ensure they are in a logical order, with the most critical variables at the top.
• Impact Column: In this column, you will input the impact or effect of each variable on the outcome. This can be a numerical value or a percentage, depending on your data.

Step 2: Sort Your Data

To create a meaningful tornado chart, you need to sort your data in descending order based on the impact column. This ensures that the variables with the most significant impact are at the top, forming the “tornado” shape.

• Select the data range, including both the variables and impact columns.
• Go to the “Data” tab and click on “Sort” in the “Sort & Filter” group.
• In the “Sort” dialog box, select the impact column as the “Sort by” option and choose “Largest to Smallest” or “Smallest to Largest” as the order.
• Click “OK” to apply the sort.

Step 3: Create the Chart

Now, it’s time to create the tornado chart itself. Excel provides a built-in chart type for this purpose, making it a straightforward process.

• With your sorted data selected, go to the “Insert” tab and click on “Insert Column or Bar Chart” in the “Charts” group.
• Choose the “Stacked Bar” chart type.
• A basic tornado chart will appear, with the variables on the vertical axis and the impact values stacked on top of each other.

Step 4: Format the Chart

To make your tornado chart more visually appealing and informative, you’ll need to apply some formatting.

• Right-click on one of the bars in the chart and select “Format Data Series” from the context menu.
• In the “Format Data Series” pane, under the “Fill & Line” section, select “No fill” for both the border and the interior. This will make the bars transparent, allowing you to see the gridlines.
• Under the “Series Options” section, set the “Gap Width” to 0% to make the bars touch each other, creating a continuous tornado shape.
• Adjust the chart title, axis labels, and legend as needed to ensure clarity.

Step 5: Add Error Bars (Optional)

If your data has a level of uncertainty or variability, you can add error bars to your tornado chart to represent this.

• Right-click on one of the bars again and select “Add Error Bars” from the context menu.
• In the “Format Error Bars” pane, under the “Error Amount” section, choose “Custom” and specify the error values or standard deviations.
• Adjust the line style, color, and width of the error bars as desired.

Tips and Tricks

• Data Normalization: If your impact values are on different scales, consider normalizing them to ensure an accurate comparison. Divide each value by the maximum value to get a percentage representation.
• Labeling: Add data labels to your chart to display the impact values directly on the bars. This can be especially useful when presenting to an audience.
• Custom Colors: Experiment with different color schemes to highlight the most critical variables and make the chart more visually engaging.
• Axis Scaling: Adjust the axis scaling to ensure the chart is not distorted. Consider using a logarithmic scale for a more accurate representation of the data.

Conclusion

Tornado charts are a valuable tool for data analysis and decision-making, allowing you to quickly identify the most influential variables. By following this guide, you can create tornado charts in Excel with ease, making your data more accessible and impactful. Remember to keep your data organized, sort it effectively, and apply the right formatting to ensure a professional and informative visual representation.

FAQ

Can I create a tornado chart with multiple sets of data?

+

Yes, you can create a tornado chart with multiple sets of data by using a clustered bar chart. Simply select all the data sets and insert a clustered bar chart. Adjust the formatting and labels as needed.

How do I handle negative impact values in a tornado chart?

+

Negative impact values can be included in a tornado chart by changing the chart type to a stacked column chart. This will ensure that negative values are displayed correctly.

Can I add a trendline to my tornado chart?

+

Yes, you can add a trendline to your tornado chart to identify patterns or trends in the data. Right-click on the data series and select “Add Trendline” to add a linear, exponential, or other type of trendline.