How to create a stacked bar, line combination chart

[Guest]

I am having trouble creating a stacked bar and line combination chart. The
standard excel charts offer a bar and line chart option, but does not seem to
offer a
stacked bar and line option.

Has anyone done this type of chart?

 

[robotman]

I just asked a very similar question, but didn't get any answer.
However, I did figure out a way to get it to do what I wanted.

1. Start with a plain stacked bar graph and chart everything you want
in that format. If you choose 2 columns for your X-labels, Excel is
smart and will give you 2 levels of X-labels and will space your chart
columns according to the row spaces in your data layout.

2. Put your line data next to your stacked bar data, but don't graph
it.

3. Go to the chart and Add a new series under source data to the
chart.

4. After adding this new series, click on it, change the axis to the
2ndary axis. And choose change the chart type to "line."

5. If you had spaces in your data table, put =N/A() function in the
spaces if you want to connect the data points in your line. This will
but #N/A in your table (which you can make white if you don't want to
see it), but it is the only way to get Excel to continue the lines from
point to point.

Hope that made some sense... good luck!

 

[Jon Peltier]

A little easier:

Make your chart with all of the data, line and stacked column (note:
vertical bars are called columns in Excel). Select the series you want as a
line, and using Chart Type from the Chart menu, change it to a line.

You can keep the line series on the primary axis if you want.

- Jon