This is what it looks like in Excel
Tricks for creating the chart in Excel
Kudos to The Economist: a new color scheme and a chart type that rarely appears on their blog. It's many moons ago that they did a Column-cum-Line chart with additional labels below it and we have never covered it in The Daily Graph. Unfortunately, like the usual blue-ish color scheme, the new one still has too many shades of a single color. We thought a bit more diversity would help to distinguish between the categories.
The Column-and-Line chart is mostly rather straightforward. Columns on the primary horizontal & vertical axes and the line on the secondary axes. The trick is with the labels with colored background below the horizontal axes. There are (at least) two ways to create this effect. One way is to create a filler column data series with negative values and transparent formatting. Then add another data series with the required values for the labels as negative values. The columns for that data series are formatted with the appropriate background color and transparent data labels display the values. This works fine but a number of solid white shapes are required to cover up part of the background for the labels.
We used the labels for the secondary horizontal axis to display the values below the regular horizontal axis labels. To position them one line lower, we added a carriage return before the label (using formula =char(13)&w15 in column y). The blue background is done with two data series with fixed negative values for all categories. The first data series has transparent formatting and the second one provides the blue background.
Using the labels for both horizontal axes was necessary because the value labels use a bold type whereas the regular horizontal labels are normal type.
This type of chart is quite useful. The value labels with colored background also allow for a data table with colored backgrounds, for instance to create a traffic light effect in the data table.
Our assessment of the difficulty of the chart: 7 (out of 10)
Usefulness of the chart for other purposes: 9 (out of 10)
Time it took us to create the chart: 31 minutes