Excel 2007: Columns overlay when using two y-axes 1

[Stretchwickster]

I've had a good search for the answer to this, but I'm struggling to find anything.

In Excel 2007, I've created a chart consisting of two clustered column series. Series 1 is plotted on the primary (left) y-axis, whilst Series 2 is plotted on the secondary (right) y-axis. Both series share the same x-axis.

The problem I have is that Series 2 completely overlays Series 1 such that Series 1 columns cannot be seen. Is there a way to position Series 2 columns next to Series 1 column (i.e. move the points of the two series apart)?

Unfortunately, the x-axis is a set of textual identifiers, so the x-axis data points cannot be staggered.

I'm aware that the Series 2 chart type could be changed to a line/scatter but this isn't really a suitable representation in my case.

The only workaround I've come up with so far is to give Series 2 a transparent fill, but this isn't ideal because when you hover over a data point you only see the value for the top-most series.

Any advice would be greatly appreciated.

Clive

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer." (Paul Ehrlich)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[xlhelp]

Rearrange you data as below and plot again.

[tt] Ser a ser b ser c
Jan 12 [tab]8
[tab][tab][tab] 11
Feb 13 [tab]9
[tab][tab][tab] 12
Mar 15 [tab]7
[tab][tab][tab] 14
Apr 10 [tab]11
[tab][tab][tab] 9
[/tt]

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.

 

[xlhelp]

Alternately, you could plot it as below and change a2, b2 and c2 to secondary axis

[tt] ser a1 ser b1 ser c1 ser a2 ser b2 ser c2
Jan 12 [tab]8 [tab]0 [tab]0 [tab]0 [tab]11
Feb 13 [tab]9 [tab]0 [tab]0 [tab]0 [tab]12
Mar 15 [tab]7 [tab]0 [tab]0 [tab]0 [tab]14
Apr 10 [tab]1 [tab]0 [tab]0 [tab]0 [tab]9
[/tt]

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.

 

[Stretchwickster]

Many thanks for your suggestions - they appear to be the only way of achieving what I want.

Clive

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer." (Paul Ehrlich)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[SkipVought]

Looks like you got a nice Christmas present from these guys. ;-)

John Walkenbach has published several books regarding Charts. I have found my copy to be an extremely valuable resource. It opens many new horizons and get you thinking about solutions in defferent ways.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Stretchwickster]

Thanks for the info Skip.

Clive

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer." (Paul Ehrlich)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096