This is what it looks like in Excel
Tricks for creating the chart in Excel
The Economist surprised us with a true infographic today and we took up the challenge to replicate it in Excel. Quite possibly the most complicated Excel chart we ever did. The chart consists mostly of a Column chart formatted with missile-images. Placing the value labels in two different colors at very unusual spots is the true difficulty of this chart. And difficult it was.
We urge you to have a look at the free workbook the follow how we created the chart. In fact, there are two charts because it is practically impossible to get the image for the US and Russia on the same chart as the smaller countries. However, both charts work from the same data.
Getting 10 colums with pictures next to each other for each country requires nifty rearranging of the data. The chart with missile-images is then rather straightforward. We used Clustered Columns with a 100% overlap. This can create a Stacked Column effect but allows for partial images. Using Stacked Columns would result in an awkward looking 22nd missile for the US. The images need to have a transparent background for this trick to work. MS-Paint won't let you create transparent background but there is plenty free software that can do it.
We almost gave up getting the yellow and black value labels on the chart. We typically use hidden Columns for something like this. But that doesn't work here because the two labels need to be close to each other whilst having completely different numbers. We resorted to a bit of good old trial & error and found out that a nifty combination of Stacked Lines can do the trick. Not sure if this is documented Excel behavior but it works!
For the first label, you will need two data series. The first data series sets up the starting point for the second data series. The second data series gets a data label attached to it so that the value is properly displayed. We're using blanks in the category name to help move the label to the right. This has a slight drawback because the category name is invisible when selecting data. The other labels needs three data series: one to go back to zero, one to set up the starting point for the third data series and one data series with data label to actually display the value as a label. All Stacked Lines are formatted to make the lines itself invisible, just the value labels appear where you want them. Sounds complicated? Well, that's because it is -- have a look at the workbook to see for yourself.
The chart for the smaller countries is mostly a copy of the top chart. There is a bit of extra work required to get the value labels aligned but nothing too onerous.
Oh yes, we took the liberty of making both the top and bottom chart "left-to-right". The Economist has the top chart as right-to-left but it was complicated enough as is.
Our assessment of the difficulty of the chart: 9+ (out of 10)
Usefulness of the chart for other purposes: 7 (out of 10)
Time it took us to create the chart: Too long