In this tutorial we’ll walk through the steps to create a simple but informative Tornado Chart in Excel.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a set of data showing years, assets, liabilities and net worth:
And from this data we are going to create a Tornado chart like this:
The first thing we’ll do is copy the data to a new worksheet. Then I’ll create a separate set of data for the year, asset and liability columns, but change the liability column to be equal to the negative of the current liability values:
Then I’ll highlight this new set of data and choose to create a 2D stacked bar chart from the Home tab:
After some positioning and resizing, the chart looks very similar to what we want to accomplish. Now we only need to do a few tweaks and we’re all set!
Next, I’ll right click on the vertical axis and choose format, Then I’ll go the the Label section and choose to position it over to the left by choosing “Low”:
Notice at this point that the values are in the reverse order from what my data set shows – the data goes from 2005 to 2014, while the chart has those in the reverse order. To change this, I need to check the box for “Categories in reverse order” under the Axis Options section:
While this vertical axis data is still selected, I’ll use the font increase command on the Home tab to increase the font size so it’s easier to read.
Next, I’ll select the horizontal axis at the top and do two things: change the location to “High” and the Display Options to “Thousands”:
I’ll also kick up the font size so it looks a bit better.
Next I’ll change the font code so that the negative values appear in Red and in parentheses, with a comma separator for thousands. You’ll need to click the Add button for this to take affect:
Finally, just right click on a liability bar and an asset bar to choose the color you want them to be:
And there you have it – a great looking Tornado Chart!
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!