This blog is available in English only.

The Daily Graph
World's nuclear weapons

0 Comment
2013-06-04 16:46:23
 

The Economist newspaper publishes a fancy daily chart on its blog. In our blog we replicate the chart using standard run-of-the-mill Excel techniques. Usually we get close, sometimes it's more difficult.

   More posts...  

Here's the link to today's Economist chart:
www.economist.com/blogs/graphicdetail/2013 /06/daily-chart-99

The main tricks for creating the chart in Excel are revealed further down in this blog. You can view all the ins-and-outs by downloading the Excel workbook. Use the workbook as you like: copy, change and/or distribute it -- we are pleased when you are.

Download the Excel workbook Free Download
 

This is what it looks like in Excel

The Daily Graph

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.

The stats:

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





 
0 Comment

Send a comment

  

Send

 

So erreichen Sie uns

+49 (0)204 1348 4565

Wenn Sie Ihre Telefon-Nummer hinterlassen, rufen wir gerne Zurück.

Oder kontaktieren Sie uns per Email

         Email