How To Create Tornado Chart In Excel

It's tornado season. Don't freak out, I am talking about Excel tornado charts. Use them to visualize age and gender-wise KPIs. Here is a quick demo of interactive tornado chart made in Excel. Watch it and read on to learn how to make your own tornado in a spreadsheet.

Demo of tornado chart in Excel – with slicer interactions

When to use Tornado charts?

Tornado charts / population pyramids are very useful in below situations.

• See population distribution by gender (obviously)
• Purchase patterns by gender and ages
• Customer walk-ins by gender and time of day
• Distribution of time on page by visitor type (customer / prospect)
• Units purchased by customer types (international vs. domestic)

In general, you can apply this type of charts whenever you have a strong binary category and a detailed dimension (time of day / age / distribution etc.)

How to create tornado chart in Excel?

Just open Excel in your laptop, load your data, go outdoors in Oklahoma (if you live outside Midwestern states then catch a flight to nearest one) and wait for a tornado.

Jokes aside, to tornado charts are nothing but cleverly formatted bar charts. Let me demonstrate how to make them from a sample data of London's actual & projected population numbers.

Watch this video tutorial or read the instructions below to create this chart in Excel.

Step 1: Calculate necessary numbers for the tornado chart

This depends on your data. For the London population data-set that I am using, we need a summary like this.

I am using a pivot table to make the calculations.

Once you have the numbers by age and gender, we need to turn one of the gender values to negative.

To do this, just create a copy of the calculations, paste as linked values. Turn one gender values to negative by using – (minus) sign in the front.

We get this sort of table.

Use Paste Links option to get a copy of data. Turn one gender values -ve

Step 2: Make a stacked bar chart

Select your age by gender (with negative values for one gender) values and insert "stacked bar" chart.

You will get this.

Step 3: Format the tornado chart

We are almost done. Just format the chart using below steps.

1. Set gap between bars to 0 (select any bar, press CTRL+1 to format them and set gap width to 0)
2. Move vertical axis labels to either low or high position, so that you can read them.
3. Flip the tornado so you can see age 0 on top and 100 at bottom. To do this, select the vertical axis, go to format and click on "Categories in reverse order" option.
4. Remove -ve signs from the horizontal axis labels. To do this, select the axis, format and go to "Number" settings. Here, you can tell Excel to omit the -ve sign while displaying labels with special codes. For numbers, you can use the code 0;0;;
   Related: For more on custom formatting codes, see this page.
5. Move legend to top
6. Add relevant chart title and captions if necessary.

Here is a time lapse GIF of the formatting steps.

Formatting steps for tornado chart

Step 4: Make it interactive

Now that you have a tornado chart, you can easily make it interactive. Just move the slicer (from step 1) closer to the chart and you have an interactive tornado chart in Excel.

Download Tornado Chart Excel Template

Click here to download the full tornado chart template. Use it to learn how to make these.

Tornado Chart Alternatives

Histograms and Pareto Analysis

When you have more than two categories, then try histogram charts. You can explore distribution of all data or make it interactive (with slicers of course). See this page for details on histograms in Excel.

In-cell bar charts – when you have too many categories

Another option is to make a table visualizing everything. But a table of numbers can be dull. So make them visual with in-cell bar charts. Here is a case study of survey results from "how people in various countries spend money?".

Check out Advanced Charting page for more inspiration.

Do you make tornado charts?

I create tornado charts often, especially when I am exploring demographic trends.

What about you? Do you make tornado charts? If so, how do you make them? Please share your tips and experiences in the comments section.

How To Create Tornado Chart In Excel

Source: https://chandoo.org/wp/excel-tornado-chart/

Posted by: brenneraltaid.blogspot.com

Share this post