Clustered line and bar on two axes

[mamacate]

Hello,

I've been banging my head against a brick wall trying to create a
chart that does what I want in Excel 2003. Can anyone help me with
change the following chart, which has clustered bars but unclustered
lines into a two-axis chart with both the bars and the lines
clustered?

Here's what I have now:

http://mamacate.typepad.com/photos/chartexamples/unholygraph.jpg

Here's what I want:

http://mamacate.typepad.com/photos/chartexamples/holygraph.jpg

(You may well note from the latter graphic that my data analysis skill
exceed my freehand drawing skills.)

Thanks in advance for any advice.

Best,

Cate

 

[Del Cotter]

I've been banging my head against a brick wall trying to create a
chart that does what I want in Excel 2003. Can anyone help me
change the following chart, which has clustered bars but unclustered
lines into a two-axis chart with both the bars and the lines
clustered?

Let your first pair of X and Y axes continue to be for a clustered
column chart, but make your second X and second Y axis be for a XY
(scatter) chart. Now arrange your "line" data like so

2002small 2003small 2004small 2005small All
0
1 1 1
2 2 2
3 3 3
4 4 4
5

[...]

20
21 5 5
22 6 6
23 7 7
24 8 8
25

The first column is the X value, and you'll have to set the bar gap
width to 100% (its default is 150%) for best results. The next four
columns are for the individual markers, and the final column is a
duplicate of all four "smaller thing" series, in order to get that line
you wanted joining the different markers. Add the whole set to the
previously prepared column chart, move them to the second axis, then
change their chart type to "XY". Format the first four series as
marker-and-no-line, and set the last series as line-and-no-marker.

If you don't care whether they have different markers, forget the four
and just go with the "All" column by itself:

All
0
1 1
2 2
3 3
4 4
5
6 5
7 6
8 7
9 8
10

[...]

20
21 5
22 6
23 7
24 8
25

Leaving every fifth line blank will break up the lines and stop them
jumping the gap between clusters.

 

[Jon Peltier]

Here's an illustrated example:

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

- Jon