How to Make a Tornado Chart in Excel

A tornado chart is one of the best ways to compare two sets of data side-by-side, but it’s not a built-in option in Excel. This tutorial will walk you through exactly how to build one from scratch, step-by-step.

What Exactly is a Tornado Chart?

A tornado chart, also known as a butterfly chart, is a type of bar chart that displays two data series horizontally, with the bars for each category appearing back-to-back. The categories are listed vertically in the middle, creating a visual effect that often resembles a tornado funnel - wider at the top and narrower at the bottom.

Its primary purpose is to provide a powerful at-a-glance comparison. You can instantly see the magnitude of two different variables for the same item. This makes it a fantastic tool for things like:

• Sensitivity analysis in financial modeling to see how different variables impact an outcome.
• Market research to compare positive vs. negative survey responses (e.g., "pro" vs. "con" or "like" vs. "dislike").
• Performance comparison, like measuring website traffic this month vs. last month for different channels, or A/B testing results.

Preparing Your Data Correctly in Excel

The success of your tornado chart depends entirely on setting up your data correctly first. This is where most people get tripped up. The key is making one of your data sets negative so it displays on the left side of the chart's central axis.

Let's use an example of comparing paid ad campaign performance for two different ad variations, Ad A and Ad B.

First, set up your table with three columns:

• Column A: Campaign (or whatever categories you're comparing).
• Column B: Ad A Clicks (your first data series).
• Column C: Ad B Clicks (your second data series).

Here’s the essential trick: you need to create a helper column that converts one of your data series into negative numbers. This tells Excel to plot those bars to the left of the center line.

Step 1: Create a "Negative" Helper Column

In a new column (say, Column D), you'll create a formula that simply multiplies your first data series by -1. So, in cell D2, you'd type:

=-B2

Drag this formula down for all your rows. Your table should now look something like this:

Step 2: Sort Your Data for the 'Tornado' Shape

To get the classic funnel shape, you need to sort your data so the largest values are at the top. You can sort by either data series or by the total of both.

1. Highlight your entire data range (including the headers).
2. Go to the Data tab and click Sort.
3. In the Sort dialog box, choose the column you want to sort by (e.g., "Ad B Clicks").
4. Select Largest to Smallest for the order.
5. Click OK. Your longest bars will now appear at the top of the chart.

How to Build the Tornado Chart Step-by-Step

Once your data is prepared, creating the chart itself is a straightforward process. The type of chart we'll use is the Stacked Bar Chart, which might seem strange, but it's the right tool for the job.

Step 1: Insert a Stacked Bar Chart

1. Select the data you want to plot. This is very important: you need to select your categories (Campaigns), your negative helper column (Helper for Ad A), and your second data series (Ad B Clicks). The easiest way to do this is to hold the Ctrl key (or Cmd on a Mac) to select non-adjacent columns.
2. Go to the Insert tab on the Ribbon.
3. Click on Insert Column or Bar Chart.
4. Under the 2-D Bar section, choose Stacked Bar.

Excel will immediately generate a chart that looks a bit strange, but we’re on the right track. You should see bars going both left and right from a center zero point.

Step 2: Fix the Category Axis

You'll notice two things are wrong: the campaigns are in the reverse order of your table, and the labels are on the far left instead of in the middle. Let's fix that.

1. Right-click the vertical axis (the one with your campaign names) and choose Format Axis.
2. A formatting pane will appear on the right side of your screen.
3. Under Axis Options, check the box for Categories in reverse order. This will flip the chart to match your table's sorting.
4. Next, find the Labels section. Change the Label Position from "Next to Axis" to Low. This will push your category labels to the far left of the plotting area, which effectively puts them in the center of our chart later.

Step 3: Format the Horizontal Axis to Remove Negatives

The chart is functioning, but the horizontal axis shows negative numbers on the left, which can be confusing for viewers. We want these to appear as positive numbers.

1. Right-click the horizontal axis (the numbers at the bottom of your chart) and choose Format Axis.
2. In the format pane, scroll down to the Number section.
3. You'll need to apply a custom number format. In the category dropdown, select Custom.
4. In the Format Code box, type the following:

0,0,0

This simple code tells Excel to display positive numbers, negative numbers, and zero all as positive values without the minus sign. Click Add and your axis labels will update instantly.

Step 4: Add and Format Data Labels

It's helpful for viewers to see the exact values for each bar.

1. Right-click on one of the bars for your right-side data series (Ad B) and select Add Data Labels. Repeat this for the left-side series (Ad A).
2. The labels on the left side will appear with negative signs. To fix this, right-click those specific data labels and choose Format Data Labels.
3. Go to the Number section, just like with the axis. Apply the same custom format code: 0,0,0.

Step 5: Final Touches and Styling

Now all that's left is to make your chart look clean and professional.

• Add a Title: Click on the chart title placeholder and give your chart a descriptive name, like "Ad Performance: A vs. B".
• Adjust Colors: Click on a set of bars to select the series, then use the Chart Design > Change Colors menu or the Format tab to pick colors that make sense. Using contrasting colors (like blue and orange) helps with clarity.
• Refine the Legend: The legend will show your original data series names, including the "Helper" column. You can edit these. Right-click the chart, choose Select Data, and in the pop-up box, select each series and click Edit to give it a more logical name (e.g., change "Helper for Ad A" to "Ad A Clicks").

Common Mistakes to Watch Out For

Building a tornado chart in Excel is a process of small, precise steps. It's easy to miss one. Here are the most common issues:

• Using the wrong chart type: You must use a Stacked Bar Chart. A Clustered Bar chart will simply place the bars next to each other, not back-to-back.
• Forgetting to make one data set negative: This is the foundational trick. Without it, all your bars will extend to the right.
• Not sorting your data: If you don't sort your source data from largest to smallest, you won't get the 'tornado' shape, it will just be a butterfly chart with randomly sized bars.
• Failing to format the axes and labels: Your chart is technically correct without this step, but showing negative values to an audience that isn't expecting them causes unnecessary confusion and makes the chart harder to read.

Final Thoughts

Tornado charts are an incredibly effective way to visualize comparative data, helping you instantly communicate key differences between two groups. While Excel doesn't offer a one-click option, creating one with the stacked bar chart method gives you full control to build a powerful and custom report visualization.

Of course, building reports like this manually in Excel becomes time-consuming when you're pulling data from multiple sources like Google Analytics, Shopify, Facebook Ads, and your CRM. We built Graphed to eliminate that exact friction. You can connect all your data sources in seconds, and then just ask in plain English to build the dashboards you need--"Show me a comparison of revenue from Facebook Ads vs. Google Ads for the last quarter"--and get a live, real-time dashboard instantly, without ever exporting a single spreadsheet.