zaterdag 14 september 2013

Excel bar chart with the labels to the left when values are positive and to the right when negative

Inspired by a graph in the newspaper. A bar chart with the labels to the left when values are positive and to the right when negative. Seemed impossible in Excel.



The data starting in A1:
Provincie Bedrag
Noord Brabant  €  450,00
Zuid Holland  €  400,00
Noord Holland  €  350,00
Zeeland  €  300,00
Friesland  €  100,00
Groningen  €    50,00
Drenthe  €   -50,00
Gelderland  € -100,00
Limburg  € -200,00
Flevoland  € -250,00
Utrecht  € -300,00
Overijssel  € -350,00

In C2 put the formula:

=IF((Sheet1!b2>0),-1,1) , 

Extend it to the end of the column. 

Based on the data block create a stacked bar graph:

Put the color of the smallest series to white
Delete the regular Y-axis. 
Add lables to the smallest series, outside end.
For the lables choose category name in stead of value. 
Remove the legend.
Remove the gridlines.

You'll get a graph like:

You can make additonal changes if you like to make it look like the one I created.

You download an example here: www.walmar.nl/zips/grafiekomgekeerd.zip

Een reactie posten