Combining a 100% stacked column with a 100% stacked line using 2 groups of data

[MikeyP23]

Hi


I am trying to create a 100% stacked column which has a superimposed line chart over the top of it.


Basically % of yes and no for two separate datasets, but which share the same characteristics. The x-axis is months of the year.


[TABLE="width: 393"]
<TBODY>[TR]
[TD][/TD]
[TD]CSP Yes</SPAN>
[/TD]
[TD]CSPNo </SPAN>
[/TD]
[TD]All Yes</SPAN>
[/TD]
[TD]All No</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]Apr-13</SPAN>
[/TD]
[TD="align: right"]6</SPAN>
[/TD]
[TD="align: right"]19</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14</SPAN>
[/TD]
[TD="align: right"]23</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]May-13</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD="align: right"]10</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13</SPAN>
[/TD]
[TD="align: right"]12</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]Jun-13</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD="align: right"]7</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11</SPAN>
[/TD]
[TD="align: right"]7</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]Jul-13</SPAN>
[/TD]
[TD="align: right"]6</SPAN>
[/TD]
[TD="align: right"]9</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15</SPAN>
[/TD]
[TD="align: right"]11</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]Aug-13</SPAN>
[/TD]
[TD="align: right"]7</SPAN>
[/TD]
[TD="align: right"]6</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20</SPAN>
[/TD]
[TD="align: right"]7</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]Sep-13</SPAN>
[/TD]
[TD="align: right"]11</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18</SPAN>
[/TD]
[TD="align: right"]7</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]Oct-13</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[TD="align: right"]10</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13</SPAN>
[/TD]
[TD="align: right"]11</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]Nov-13</SPAN>
[/TD]
[TD="align: right"]10</SPAN>
[/TD]
[TD="align: right"]7</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18</SPAN>
[/TD]
[TD="align: right"]10</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]Dec-13</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]Jan-14</SPAN>
[/TD]
[TD="align: right"]6</SPAN>
[/TD]
[TD="align: right"]7</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14</SPAN>
[/TD]
[TD="align: right"]8</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]Feb-14</SPAN>
[/TD]
[TD="align: right"]8</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]Mar-14</SPAN>
[/TD]
[TD="align: right"]6</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]



I can create 2 separate charts. One for the yes/no of one set as a 100% stacked column. I then use 'no fill' on the No column to stop this from being seen.


I can create a 100% line chart for the second data type, and 'no fill' the 100% line that appears.


However, I can't merge the two charts to show the Yes % for one dataset as bars and the Yes % of the other as a line.


I am using Excel 2010 on XP.


Hope you can help. Thanks in advance.

 

[BrianMH]

Don't offset your rows. Add all columns to 1 chart as a 100% stacked bar chart. Then set the All no and All yes to the secondary axis (right click the series in the legend and click format data series). Next change those 2 series to 100% stacked line (right click the series and click change series chart type). That should do it it, I tested this with the data you provided and it works just fine.

 

[MikeyP23]

Don't offset your rows. Add all columns to 1 chart as a 100% stacked bar chart. Then set the All no and All yes to the secondary axis (right click the series in the legend and click format data series). Next change those 2 series to 100% stacked line (right click the series and click change series chart type). That should do it it, I tested this with the data you provided and it works just fine.

Thanks. That worked. The thread can be closed.