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.

	CSP Yes</SPAN>	CSPNo </SPAN>	All Yes</SPAN>	All No</SPAN>
Apr-13</SPAN>	6</SPAN>	19</SPAN>
			14</SPAN>	23</SPAN>
May-13</SPAN>	5</SPAN>	10</SPAN>
			13</SPAN>	12</SPAN>
Jun-13</SPAN>	5</SPAN>	7</SPAN>
			11</SPAN>	7</SPAN>
Jul-13</SPAN>	6</SPAN>	9</SPAN>
			15</SPAN>	11</SPAN>
Aug-13</SPAN>	7</SPAN>	6</SPAN>
			20</SPAN>	7</SPAN>
Sep-13</SPAN>	11</SPAN>	4</SPAN>
			18</SPAN>	7</SPAN>
Oct-13</SPAN>	4</SPAN>	10</SPAN>
			13</SPAN>	11</SPAN>
Nov-13</SPAN>	10</SPAN>	7</SPAN>
			18</SPAN>	10</SPAN>
Dec-13</SPAN>	3</SPAN>	2</SPAN>
			6</SPAN>	1</SPAN>
Jan-14</SPAN>	6</SPAN>	7</SPAN>
			14</SPAN>	8</SPAN>
Feb-14</SPAN>	8</SPAN>	1</SPAN>
			23</SPAN>	1</SPAN>
Mar-14</SPAN>	6</SPAN>	2</SPAN>
			21</SPAN>	2</SPAN>

<TBODY>
</TBODY>

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.