woensdag 6 november 2013

Excel: Displaying Budget Deficit of European Union Members

Today, inspired by a graph in one of our Dutch newspapers I created this one about the budget deficit of all the members of the European Union:


A lot of tricks are needed here.

Here are the data from A1:B28:

Country Deficit
Poland 4,6
Germany 0,1
Latvia -0,1
Luxembourg -1,0
Sweden -1,2
Denmark -1,7
Austria -1,9
Greece -2,0
Bulgary -2,0
Romania -2,0
Finland -2,3
Lithuania -2,5
Belgium -2,6
Italy -2,7
Czech Republic -3,0
Hungary -3,0
Slowakia -3,2
The Netherlands -3,3
Malta -3,4
France -3,8
Portugal -4,0
Ireland -5,0
UK -5,3
Spain -5,9
Croatia -6,5
Slovenia -7,1
Cyprus -8,4

  • In C1 type Requirement.
  • In C2 type -3 and drag this to C28.
  • In D1 type Label.
  • In D2 type the formula: =IF(B2>=0;-0,01;0,01)
  • Drag this formula to D28.
  • Format this columns as -0,01.
  • In E1 type Band1 and Band2 in F1.
  • In E2 type the formula: =IF(ISNUMBER(E1);"";-10)
  • In F2 type the formula: =IF(ISNUMBER(F1);"";6)
  • Drag both formula's to E28 and F28 respectively.
Now the data are ready.

  • Create a bar graph based on A1:D28.
  • Delete the legend.
  • Format the X-axis form -10 til 6 fixed.
  • Set Series overlap for the bars to 100%.
  • Select the Requirement bar.
  • Set fill to No fill.
  • Add a trendline to this bar.
  • Select the Label bar.
  • Choose Data labels -> Inside end.
  • Set label options to Category Name.
  • Plot series on Secondary axis.
  • Set the color to No Fill.
  • Format the secondary X-axis form -10 til 6 fixed.
  • Set the Horizontal Category Labels for Label to B2:B28.
  • Set the Y-axis labels to Low.
  • Add the columns Band1 and Band2 to the graph (they are automatically linked to the secondary axis; if not: do so)
  • Set the color of both bars to grey.
  • And transparancy to 50%.
  • Set the gap width to no gap.
  • Remove the gridlines.
  • Set both X-axis labels to none.
  • For the Deficit values choose Invert if negative and pick two different colors.
  • Set the gap width to 100%.
  • Add a chart title, background black, font color white: EU-requirement -3%
  • Put this right on top of the trendline.
Now the graph should look like the one I originally showed.
You can also download the file budgetdeficit.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing
Een reactie posten