In foreign sources, this type of chart is called a tornado-chart. From our compatriots, I heard the options “pot diagram” and “butterfly diagram” – who looks more like 🙂
This type of chart is convenient to use for a visual comparison of two data sets, for example:
- we and competitors in the main areas (goods, services)
- the number of men and women of different ages among buyers or viewers
- this year and last year according to the main KPIs, etc.
Building such a thing is not very difficult, but there are a couple of small ambushes along the way – let’s deal with them.
As a basis, we take the usual bar chart with accumulation (Stacked Bar Chart):
To make the columns diverge in different directions, and not add to each other, we use a simple trick – add a minus sign in front of the numbers of one of the sets. Then negative and positive values will go in opposite directions from the Y-axis. The easiest way to do this is with a special paste, namely:
- enter minus 1 into any empty cell and copy it
- highlight the numbers in the data for one of the sets (for example, population by city in the range B5:B19)
- press Ctrl + Alt + Vto bring up the Paste Special window
- select options The values (Values) и Multiply (Multiply) and press OK.
Now we can select our entire data table (A4:C19) and build a stacked bar chart on the tab Insert (Insert). The result will already look like what you wanted:
To complete the picture, it is better to move the vertical Y-axis all the way to the right so that it does not interfere in the middle of the chart. To do this, right-click on the horizontal axis, select the command Axis Format (Format Axis) and then in the group The vertical axis intersects option Maximum axis value (Maximum Value).
You may also want to hide the minuses in front of the numbers on the diagram. The easiest way to do this is to apply a tricky custom format to cells with source data that does not display a minus sign. To do this, select the original cells (B5:B19), open the Format Cell window and, by selecting the option All formats (Custom), enter in the field A type the following mask:
The minuses will disappear in the cells and, as a result, on the chart.
- How to create custom formats in Excel sheet cells
- How to Build a Waterfall Chart
- How to build a plan-fact chart in Excel