Creating a Tornado charts in LibreOffice Calc

Tornado charts help us to display opposite sides of one and the same process. This is not just a nice view, but the intuitive display of the process. In this article I will try to show a quick and easy way to build this type of charts in LibreOffice Calc. For example, such as this:

File with Examples

In LibreOffice Calc a Tornado charts are based on 'Bar' charts with bars are directed to different sides from the axis X. This chart type is often used to evaluate a sensitivity and a effectiveness of projects and sociological research. Therefore, in the classical form the values in the chart are sorted in such a way that at least on one side them decrease. This diagram gives view similar to a tornado, and allows us to see the most important factors. As an example for the chart I use events which years are used as the category. As you know, sorting by value makes a chart unreadable with timeline. And in this case it is necessary to consider whether really the trend be seen on the chart or you should choose a different type chart. In my opinion, in the present example, the trend is visible very well, which is why I have selected it for this article. Again, using the non-classical example, I hope to expand your range of application of this convenient chart.

Creating a Simple Tornado charts

For example of creating of the simple Tornado chart in LibreOffice Calc I took the international population migration of the Russian Federation for 2000 - 2012 years, according to Rosstat. The table will look like on Illustration below:

The column C is a service column for the chart. It can be hidden after the chart creating. Values in it equal corresponding values from column B with sign minus. You can use the formula =-B4 in the C4 cell and extend it down to the C16cell. And so, let us create a chart:

Select the necessary range:

Select cells A4:A16;
Press the Ctrl key and...;
Select the C4:D16 range;
As a result, column B remains not selected.

Start the Chart Wizard.
Choose the Bar type and Normal of the chart in the first step of Wizard.
If you use the text format for years, then the Data Range step can be skipped. Otherwise, in this step you need to choose the The first column as label.
In the Data Series step we need to point names to Data Series. It allows us to the do a presentable legend.

Choose the Column C in the Data Series field and Name in the Data ranges field;
Click on the button which the right from the field Range of Name;
Choose the B3 cell.
Repeat steps 5.1 – 5.3 for the Column D, but choose D3.

Choose Bottom under Display legend, fill the field Title as International migration of the Russian Federation for 2000 - 2012 years.
Click the Finish button.

We should get something such on Illustration:

Do not go out from the editing charts. Now we need to bring view of the Tornado chart to the desired result manually.

Let's start with the Y axis.

Click the right mouse button on the Y axis (for Bar charts it is the horizontal axis) and choose the Format Axis... in the contest menu;
Uncheck the Major interval in Scale tab and type 5. I think that it will be more convenient for this chart.
Remove negative numbers and extra zeros from the axis. For this, go to the Numbers tab, uncheck the Source format and type in the Format code field [>0] 0.
Click Ok button.

Let's start with the Х axis. In this case years should be from top to down and the X axis should be alongside with bars.

Click right mouse button on the Х axis (for Bar charts it is the vertical axis) and choose the Format Axis... in the contest menu;
Check the Reverse direction in the Scale tab.
Click Ok button.
Select the X axis and press the Del key. This step needs in order to not don’t break the chart.

Now we recreate the X axis in right place.

Click right mouse button in the Chart Area and go to the Insert/Delete Axes.
Choose the X axes uder the Secondary Axes. And click Ok button. The X axes appear right.
Click right mouse button in the X axis and go to the Format Axes...
Choose Start in the Cross other axis at drop-down menu in the Positioning tab. And click Ok button.

Now we need to move together bars in the chart in order to have bars in one line.

Click right mouse button in any bar and go to the Format Data Series in the context menu.
Type 100% in the Overlap field of the Options tab.
Check the Include values from hidden cells in this tab too in order to have opportunity to hide the service column.
Click Ok button.

The last what should be done, it is inserting label for the Y axes (right click in the Cart Area and go to the Inset Titles… context menu) and align elements, if it is needed (it does manually).

I think that for this example it can be better, if right bars will be green. You can change color, if click right button in a bar and go to the Format Data Series in the context menu, and choose color in the Area tab.

I would like to note that Reverse direction for secondary axes does not change the direction of the Data Series, but only labels on the axe. So, we should do the Reverse direction in main axes and delete it, because the position changing breaks view of bars in different sides. The situation has been continuing since “ancients” times. But it is not critical, we just should know about it.

Creating of the Tornado chart with accumulation (stacked)

A Tornado chart with accumulation in LibreOffice Calc is based on the "Bar Stacked" chart. For its creation we take an extended table of the previous example. The table look like this:

The columns B (out of RF (total)) and G (into RF (total)) is not needed for this chart. But it does logically the complete table. The column E takes values from the column C with sign minus, and the column F takes values from the D with minus. They are connected with help of the formula like in first example. Let us create a chart:

Select the necessary range:

Select cells A4:A16;
Press the Ctrl key and...;
Select the E4:F16 range, and without released button, select H4:I16;
As a result, columns B, C, D and G remain not selected.

Start the Chart Wizard.
Choose the Bar type and Stacked of the chart in the Chart Type step of Wizard.
If you use the text format for years, then the Data Range step can be skipped. Otherwise, in this step you need to choose the The first column as label.
In the Data Series step we need to point names to Data Series. It allows us to the do a presentable legend.

Choose the Column E in the Data Series field and Name in the Data ranges field;
Click on button which the right from the field Range of Name;
Choose the C3 cell.
Repeat steps 5.1 – 5.3 for Columns F, H and I, but choose D3, H3 and I3 respectively.
Since the migration with non-CIS countries is less, I propose to change the order range. It will facilitate easier the perception of the chart. Rows in the stacked bars will accumulate in a positive side, if they are positive, and the negative side, if they are negative. Therefore, we put the data range of "out of RF to non-CIS countries", followed by "out of RF to CIS", then "into RF from non-CIS countries" and at the end already "into RF from CIS."
Select a range and move it with help arrows at the bottom.

Choose the Bottom under the Display legend in the fourth step, fill the field Title as International migration of the Russian Federation for 2000 - 2012 years.
Click Finish button.

Then we repeat the same steps as in the previous example except that we don’t need to move together bars. Short:

Setting the Y axis.
Reverse direction for the Х axis and delete this axes.
Create Secondary X Axes and place it in start position.
The last what should be done, it is inserting label for Y axes and align elements.