zaterdag 9 november 2013

Excel: Complex Bar Graph Mainly Based on the Positioning of the Data

Just wonder how newspapers create their charts. My aim is to rebuild them in Excel.


For this example I had to do a lot of repositioning of the data to make it look like this.

Here are the data from A1:H31

Male Female band1 band2
Pedagogy 1990 -21% 79% -100% 100%
2012 -5% 95% -100% 100%
Psychology 1990 -37% 63%
2012 -25% 75%
Communication Sciences 1990 -37% 63% -100% 100%
2012 -35% 65% -100% 100%
Medicine 1990 -47% 53%
2012 -37% 63%
Law 1990 -52% 48% -100% 100%
2012 -38% 62% -100% 100%
Biology 1990 -54% 46%
2012 -43% 57%
Administration 1990 -62% 38% -100% 100%
2012 -57% 43% -100% 100%
Business Economics 1990
2012 -68% 32%
Engineering 1990 -75% 25% -100% 100%
2012 -70% 30% -100% 100%
History 1990 -55% 45%
2012 -64% 36%

The chart is based on A2:A32 column as the Y-axis and C2:F32 as the real data.

Band1 and band2 provide the data for the grey background. Both series are linked to the secondary axis. Transparancy 80%.

To make the X-axis look like this I used: 0%;0%

Male and female are simply rectangles linked to C1 and E1.

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

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