vrijdag 29 november 2013

Excel: Separated X axis

Very nice graph in a Dutch newspaper (de Trouw) which I remade in Excel.


Here are the data from A1:D5:

Liquor Price (%) Liquor Sales (%) tussen
2012 2,4 -4,0 -1,5
2013/1 3,8 -10,4 -1,5
2013/2 3,7 -9,6 -1,5
2013/3 3,8 -8,1 -1,5
  • Create a Stacked Bar Diagram based on the range A1:D5.
  • Set the color of the fourth part to No fill.
  • Add a line graph based on column two.
  • Add labels for this line.
  • Choose Above for the labels.
  • Set the color of the line graph to No color.
  • Create a name label2:
=Blad1!$C$2:$C$5+Blad1!$D$2:$D$5
  • Add this name to the graph as a line graph.
  • Add labels for this line.
  • Choose Below for the labels.
  • Set the color of the line graph to No color.
  • Add a another line graph based on column four.
  • Add this line to the Secondary Axis.
  • Change the secondary Y-axis in a way that the horizontal axis crosses at -1,5.
  • Both axes should run from 6 to -14.
  • Remove both Y-axis.
  • Change both X-axes so that the line is black.
  • Remove the labels of the secondary horizontal axis.
  • Add two boxes and link those to B1 and C1 respectively.
  • Format the boxes and the font color like I did in the graph.
Then, the outcome should look like the picture.

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

Een reactie posten