I generally preface an explanation like this by saying "Don't use the
built-in custom chart types provided in Excel." I make a joke about the
"built-in custom" oxymoron, then I point out that Excel exerts much
control over the built-in types, usually at inopportune times, while you
can make the chart do whatever you want if you build it yourself.

This sets the stage for the rest of John's description.

- Jon

John Mansfield wrote:

Vivi,

As an example, to set up the combination Line €“ 100% Stacked Column Chart,
assume you have your data set up like below in the range A1:E6.

Ser 1 Ser 2 Ser 3 Ser 4
a 5 5 5 4
b 4 4 4 3
c 5 5 5 4
d 6 6 6 6
e 7 7 7 5

Select the entire range. Then using the Chart Wizard, create a 100% Stacked
Column Chart. After the chart is created, you should have four series
visible.

Click on the top series of the chart (series 4). Right-click on your mouse
and select Chart Type. In the Chart Type dialog box - Standard Types tab,
choose the Line chart and hit OK. The line should appear near the bottom of
the chart.

Click once on the line. Right-click on your mouse and select Selected
Object. In the Format Data Series dialog box, go to the axis tab. Choose
Plot Series on secondary axis and hit OK.

You should now have a chart with percentages on the left Y axis and numbers
on the right Y axis. You will need to adjust the axis scale for the right Y
axis to meet your needs.

Excel determines what data appears where by what youve entered in the
source i.e. spreadsheet and by what axis youve assigned that source to.