zaterdag 5 oktober 2013

Excel: How to Display Incomplete Data Compared to Complete Data

Inspired by my newspaper this morning, here is my idea.


And here are the data from A1:D14:

Apples Incomplete Percentage
2001 100
2002 200
2003 200 50%
2004 400
2005 500
2006 600
2007 400 60%
2008 800
2009 900
2010 1000
2011 1100
2012 1200
2013 650 70%

What did I do next?

  • Create a clustered column graph based on A1:C14.
  • Link the second column to the secondary axis.
  • Set the gap width to 300 % twice the percentage of the other one.
  • Format column D2:D14 with a custom number format like this: 0"%"
  • Change the horizontal (Category) axis labels of the second column to D2:D14.
  • Switch the maximum of the secondary Y-axis to a fixed 1400.
  • Delete the secondary Y-axis.
  • Add data labels to the second column.
  • Switch the data labels to category name instead of value.
And you should end up with the graph above.

You can also download the file dataincomplete.zip through this link:
Een reactie posten