How do I create my own custom combination charts?
 

I am trying to create a line-column custom chart but I would like the column
chart to be a 100% stacked column not just a simple column chart as
determined by the custom chart in Excel. I can't seem to figure out how to
do that. Also, how does Excel determine what data points show up on the
column part of the chart and what data point show up in the line part of the
chart?

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.

--
Regards,
John Mansfield
http://www.pdbook.com


"Vivi63" wrote:

I am trying to create a line-column custom chart but I would like the column
chart to be a 100% stacked column not just a simple column chart as
determined by the custom chart in Excel. I can't seem to figure out how to
do that. Also, how does Excel determine what data points show up on the
column part of the chart and what data point show up in the line part of the
chart?

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.