Bar Graphs with two diferrent axis

[loonger]

Hi there,

Excel 2012 user.

I am trying to create two vertical axis bar graphs with the Primary axis for period Q1 to Q4 and the secondary axis for FY. I have attached what I have done so far and have successfully converted all but one FY bar graph to secondary axis. Once I try to select the last one (dark gray bar called "2011 FY" on the very far right), the FY bars disappears.

My main purpose is to get both Q1-Q4 & FY on the same graph and the reason I plotted FY on the secondary axis is because I didn't want the FY $ scale to massively skew the entire graph.
Any help would be greatly appreciated.
Thanks in advance.
Sample Report 06JAN14 - Bar graph with two axis.xlsx
Kevin

[DBY]

Hello Kevin
Is the attached example what you're looking to do? If so, hopefully you can work it out from the example chart.

DBY

[loonger]

Hi DBY,

Thanks for your help but that is not my intended result.
I need it exactly like my graph I attached originally but when I try to change the last bar (dark grey one at the far right hand of the chart) to a secondary axis, the whole chart changes and my FY bars disappear. I need the FY for all 4 years in its own grouping so that it can have their own scale.

Kevin

[MrShorty]

I can't recreate the problem. When I open your file, I simply right click on the 2011 FY bar/column -> format series -> move to secondary axis and it falls in with the other FY bars/columns just as one would expect (see if it looks right in my attachment).

[Andy Pope]

additional change series 1 to use all columns for category labels
=SERIES('Actual GAP, AR'!$A$3,'Actual GAP, AR'!$B$1:$F$1,'Actual GAP, AR'!$B$3:$E$3,1)

On the secondary vertical axis fix the Minimum Scale to zero, as this is a column chart.

You migh also consider using different colours for the last cluster to emphasize the fact they have a different scaling.

[DBY]

Hi
Not sure I understand.

I need the FY for all 4 years in its own grouping so that it can have their own scale.

This is what my example chart does. The 'FY's' are clustered together, one bar for each year on the secondary axis with their own scale as in your example and MrShorty's. The only difference is that I included the 2009 figures and placed them in chronological order with slightly different colour formatting.

DBY