Combo charts: stack bar with target line

[Martin James Thornhill]

I'd be grateful for your assistance on this one!

I need a combo chart as follows:

series 1 and series 2 = stackable column/bar chart, time on X-axis, value on
Y-axis. These show two different types of "actual" results, the sum is the
total actual result.

series 3 = a line, following the same X-axis, may need to be shown on a
secondary Y-axis. This shows the total "target" or "budget" figure.

I used the stack-bar chart and found that series 3 could be plot only as a
bar, not a line. I tried everything to make it appear as a line, but
couldn't. In desparation, I went back to the chart type selection and the
type I want isn't there. But I noticed that I could save my custom chart....
aaagh! I need to make my custom chart first!

I'm not a programmer, just an ordinary accountant with a spreadsheet
nightmare!

Kind regards

 

[Stephanie Krieger]

Hi,

What you want to do can be really easy -- and doesn't
require a custom chart type (though you can save it as
one when you're done, if you need this same type of chart
often).
Just two quick steps -- here's what you do:
1. Create your chart with all 3 series as part of your
stacked columns.

(Note that, columns will be easier to work with than
bars, because a bar chart is plotted with the x-axis
vertical and the y-axis horizontal -- but the line series
will still be plotted as a standard line chart (or a
column chart) is plotted (i.e., x-axis horizontal and y-
axis vertical.)

2. On the chart, select the series you want to be a line
and go to Chart, Chart Type -- on the Standard Types tab
of the Chart Type dialog box, you'll see that the box
labeled 'Apply to Selection' is checked, because you
selected the desired series before opening this dialog
box. Just select your preferred line chart type from the
options on the Standard Types tab, and Click OK.

That's it -- you're 3rd series should now be a line!

If this is something you do often (with the same number
of series and the same series always being the line), you
can now (with your new chart selected) go back to Chart,
Chart Type. On the Custom Types tab, click the User-
Defined option -- and then click the Add button that
appears, to name your new chart type (which will be based
upon your new active chart). That will then be an
available user-defined chart type going forward when you
create a new Chart.

Hope that's helpful. Let me know if I answered all of
your questions.

Best,
Stephanie Krieger
author of Microsoft Office Document Designer (from
Microsoft Learning)
email: MODD_2003 at msn dot com
blog: arouet.net

 

[Martin James Thornhill]

Perfect!
Many thanks

Hi,

What you want to do can be really easy -- and doesn't
require a custom chart type (though you can save it as
one when you're done, if you need this same type of chart
often).
Just two quick steps -- here's what you do:
1. Create your chart with all 3 series as part of your
stacked columns.

(Note that, columns will be easier to work with than
bars, because a bar chart is plotted with the x-axis
vertical and the y-axis horizontal -- but the line series
will still be plotted as a standard line chart (or a
column chart) is plotted (i.e., x-axis horizontal and y-
axis vertical.)

2. On the chart, select the series you want to be a line
and go to Chart, Chart Type -- on the Standard Types tab
of the Chart Type dialog box, you'll see that the box
labeled 'Apply to Selection' is checked, because you
selected the desired series before opening this dialog
box. Just select your preferred line chart type from the
options on the Standard Types tab, and Click OK.

That's it -- you're 3rd series should now be a line!

If this is something you do often (with the same number
of series and the same series always being the line), you
can now (with your new chart selected) go back to Chart,
Chart Type. On the Custom Types tab, click the User-
Defined option -- and then click the Add button that
appears, to name your new chart type (which will be based
upon your new active chart). That will then be an
available user-defined chart type going forward when you
create a new Chart.

Hope that's helpful. Let me know if I answered all of
your questions.

Best,
Stephanie Krieger
author of Microsoft Office Document Designer (from
Microsoft Learning)
email: MODD_2003 at msn dot com
blog: arouet.net

 

[Jonathan B]

That worked for me too!

Perfect!
Many thanks

 

[Tuxla]

Thanks, this is very useful, but can it be done with bars? I need a chart
with horizontal bars (showing a % by customer) and I'd like to add an
average, but the line always ends up horizontal and creates new x and y axes

Thanks

 

[Andy Pope]

Hi,

Use a xy-scatter chart rather than a line chart in order to get the vertical
datum.
http://www.andypope.info/charts/averageline.htm

Cheers
Andy

 

[Tuxla]

that's great, thank you

Hi,

Use a xy-scatter chart rather than a line chart in order to get the vertical
datum.
http://www.andypope.info/charts/averageline.htm

Cheers
Andy

 

[smcgee01]

I have the horizontal line but want to provide a value for it on the y-axis,
is there a way to do it?

 

[Jon Peltier]

If you use one of these techniques, you can simply add a data label to an XY
point, using the Show Value option:

http://peltiertech.com/Excel/Charts/AddLine.html

- Jon