Excel: Butterfly Chart, Percentages Combined with Amounts

Based on an example in the Dutch magazine Elsevier I created this butterfly chart.

Here are the data from A1:C29:

Country	Percentage	Amount in Billions
Austria	1,1%	2,51
Belgium	1,5%	3,953
Bulgary	2,1%	0,578
Croatia	1,7%	0,745
Cyprus	2,1%	0,366
Czech Republic	1,1%	1,726
Denmark	1,4%	3,453
Estonia	1,9%	0,326
Finland	1,5%	2,846
France	2,3%	45,82
Germany	1,5%	35,591
Greece	2,5%	5,083
Hungary	0,8%	0,806
Ireland	0,6%	0,901
Italy	1,7%	26,433
Latvia	0,9%	0,202
Lithuania	1,0%	0,317
Luxembourg	0,6%	0,265
Malta	0,6%	0,041
Poland	1,9%	7,272
Portugal	1,8%	2,937
Romania	1,2%	1,698
Slovenia	1,2%	0,414
Slowakia	1,1%	0,797
Spain	0,8%	8,966
Sweden	1,2%	4,826
Netherlands	1,3%	7,648
UK	2,5%	47,294

To this I added a column Pillar in D1. From D2:D29 I filled in the value -10.

I created three names:

Amountlabels	=Blad1!$C$2:$C$29
Percentage	=Blad1!$B$2:$B$29*-1
PercentageLabels	=Blad1!$B$2:$B$29*-1

Then I created a stacked bar graph based on the four data columns. I added to more series based on the names AmountLabels and PercentageLabels. The order should be like this:

The bar which refers to pillar I changed to no color. I added data labels to this one which I centered.

In the graph I based the column Percentage on the formula 

=Blad1!Percentage

AmountLabels and PercentageLabels should also be set to no color with the data labels set on, with the position respectively set to Inside End and Inside Base.

The horizontal axis is fixed on minimum -20 and maximum 60 and removed.

To replace the horizontal axis labels I created a label range from A32:AF33 as you can see in the file which you can download. Of this range I added the lower one as a line graph to the chart, linked tot the upper one as data labels. The color is then set to no color and the data labels switched on, changed into Category and positioned Right.

You can download the file Butterfly.zip through this link:

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