How to build a Tornado chart in Excel

Hello! In this post I want to explain step by step how to build tornado chart in Excel. If you are a little confused about what a tornado chart is then let me show you visually. Basically a tornado chart looks like this (the typical graph of population distribution by age):

Well, the first thing we need before we attempt building this chart is get enough coffee and good data.

In my case the coffee is served, and the data (good of course) comes from the ONS in the UK. 

Once we have the coffee and the data let's go to the Step by step guide.

1. Modify the table by adding 3 more columns

We must add 3 more columns to the table, one for Filling males, another for females and one for the space between. The maximum value in the table above (excluding total) is 2.375 m, so that we can make bars up to 2.5m on each side to make it look better.


So to calculate the number of Filling M (filling for males), we subtract the value to 2.5m. In the same way we built the Filling F (filling for females). And we set the gap at 500,000, which is large enough value to allow the labels of the different ages to be displayed properly.

2. Create a stacked bar chart

What we do now is to create a stacked bar chart for the table we have just built. Make sure the totals have been eliminated.

3. Change the order of the series and change the gap width between the series

Now pointing to the chart, we right-click on the mouse and go to select data. Once there we alter the order of the series, putting Filling M first, then Males, Gap, Females and finally Filling F.

We also changed the width of the gap between to series to 0%. To do this, simply click on one of the series, right-click, and go to format data series. Once there, set the gap width to 0%. The result would look like this:

4. We hide the series we do not need

Now is the time to hide Filling series M, F and Gap. To hide a series click on a bar in the series, and make sure all the bars in the series are selected, right click and go format data series. We now go to fill and select 'no filled' and then go to borders and select 'no line'. You have to repeat this for each of the series that you want to hide.The result would now look like this:

5. We add labels to the Gap series with category name instead of value

If we click in between the Males (blue) and Females (orange) series we can find the series called Gap we have just hidden in step 4. We right-click, and select add data label. This will add the value label to the series Gap. Clicking in any of the value labels, and ensuring that all labels are selected we right-click again and select format data labels. It is here where we change the content of the label, setting it to category name instead of value.

6. We now format the chart

Now it would be nice to start formatting the chart a little. We can remove the horizontal and vertical axes and legend, you can do it by clicking on one of the axis, and press Del  or Delete key, depending on your keyboard. Do the same with the legend.

7. We now add an extra series to the horizontal axis

Ok now we begin with the hardest part. Hope till now it has been ok. 

We need a new horizontal axis to display the values we want: Something like:

2.5M 2M 1.5M 1M 0.5M 0M 0M 0.5M 1M 1.5M 2M 2.5M

We are going to have to add a new series. Right now the X axis has the values displayed in the table below under X series, what we want to do is to show those values with the value displayed in that same table under Y, but instead of showing the value we will only show the label. How do we do this? Let's see...

Clicking on one of the series of the chart, right-click and select data, and then click on add. The edit series dialog box comes up, and we enter as series name the cell where it says Axis and as values of the series, select all the values under X (0 to 5,500,000). The resulting chart is as follows:

8. Change the chart type for the series Axis to XY (scatter)

Clicking on the Axis series, select change series chart type and change to XY (scatter). We are mixing different chart types within one chart so it is important now to... have a sip of your coffee. 

Ensure the combo option is selected, and change the chart type for the series Axis to XY (scatter) and the remaining series stay as stacked bar chart. It is illustrated in the image below.

The result of this action is:

9. We edit the series Axis to include the value Y

We now click on any series and go to select data, then select the Axis series and click edit. Returning to the table in step 7 we now select the X values as the series X values and the Y values as series Y values.

10. We now edit the vertical axis of the series Axis to make it appear that the points lie on a straight line

Now clicking on the vertical axis, select format axis and changed the maximum limit value axis (under Bounds) to a value high enough so that the values of the axis series visually appear to be in a straight line. Let's say 100,000.

11. We add labels to the Axis series, and place them below points and hide the points

Clicking in any point of the Axis series will select the whole series, we now right-click and go to add data label. The series will now have data labels. Clicking in one of them will select all, we right-click again and go to format data labels and change the label position to below. Once done, select the series again, right-click and go to format data series and under markers > marker options, select none.

12. We now change the labels of the series Axis manually

Double-clicking in each value of the labels will select only that label, what we want is to replace it with the label value of the table in step 7. If you select the first on the left (2.5), double-click in it and once selected (make sure it is only that label selected), write in the formula bar = and the cell where the label is (table from step 7). Repeat this step for all the labels.

13. And finally we add title and legend, leaving only the relevant series

We are now in the final step, with no coffee left, but with a pretty chart that we have built ourselves. Add a title, the legend with only the relevant series, and change the colour of the series if you like. And that's it!

Download

If you would like to download the file used in this post, click on the following link:

Tornado chart