Indzara

How to Create a Tornado Chart in Excel?

A tornado chart is a special type of chart built by modifying a bar chart. This is used to compare your data among two categories with a single chart.

Let us take sample data of headcounts of various departments in two locations, as shown below:

Ready to save time on your next presentation? Explore our Data Visualization Toolkit featuring multiple pre-designed charts, ready to use in Excel. Buy now, create charts instantly, and save time! Click here to visit the product page.

Step 01: Prepare the data

Before we begin with the chart creation, first select all data and press CTRL+T to create a table.

Now, select all data, go to Sort & Filter

From here, sort the table in ascending order with the Location A column as shown.

With this, the data should look like this:

Step 02: Create the chart

Now, select all the columns and insert a clustered bar chart.

With this, a default bar chart gets inserted into Excel.

Step 03: Format the Axis

To achieve the tornado shape, we’ll move one of the series to the secondary axis.

i. Right-click on any of the Location B columns and go to Format Data Series

ii. This opens a format pane to the right. From here, under series options, check the Secondary Axis.

iii. Now, right-click on the secondary axis (which is on the top) and go to Format Axis.

iv. Under Axis Options, modify the minimum bounds to -100 and maximum bounds to 100.

Also, check the “Values in Reverse Order” option.

Why did we modify the bounds to -100 and 100?

Find the highest value in the dataset and use a slightly higher number as your bounds to create the tornado chart.

Similarly, right-click on the primary axis and modify the bounds ONLY.

With this, your chart would look like this:

Step 04: Remove the Secondary axis and format axis scales (values)

Now click on the secondary axis and press the delete key.

We do not want the negative values in our horizontal axis, hence we’ll format the same to have only positive values for both the series of data.

i. Right-click on the horizontal axis, and go to the format axis.

ii. From the axis options, go to Number. Under this, choose a Custom category.

iii. Under Format Code use “###0;###0” and click on Add.

With this, the horizontal axis gets modified and your chart will look like this:

Step 05: Format the Y axis and data labels

i. Right-click on the Y-axis, and go to format axis.

ii. Under Labels, choose the position as Low.

iii. Click on the chart, and use the “+” icon that appears on the top of the chart to add data labels.

iv. Click on the data labels of Location A and from the Label options, position them on the inside base.

Repeat the same for the labels of Location B.        

After this step, your chart should look as shown below:

Step 06: Modify the gap width, format the bars

By reducing the gap width, we’ll reduce the gap between the bars for each series. To get a proper tornado effect, modify the gap width for both series as shown.

i. Click on the left side bars (of Location B) and reduce gap width:

ii. Go to Fill & Line and modify the fill color as needed.

Repeat the same for the right-side bars. Ensure that the gap width is the same.

With this, our tornado chart is almost ready.

Step 07: Format the chart

Let’s apply some standard formatting for a better visual.

i. Add a suitable chart title and format it using the Home Ribbon.

ii. Click on the Horizontal (Value) Axis Major Gridlines and remove the line as shown:

iii. Click on the legends and re-position them to the top.

iv. Click on the chart, and format the border as shown:

With these 7 steps, our Tornado chart is ready!

Leave a Reply

Your email address will not be published. Required fields are marked *

Join our weekly newsletter today!

As a thank you, we send a free Column Chart Guide to your inbox.