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:
https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing
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.
- 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
Reacties