column chart stacks data when i add a secondary vertical axis

[scott]

I am creating a column chart. I have two sets of data that need to be
compared but with a different vertical axis. When I select "format data
series" to indicate which sets of columns will utilize the secondary axis
excel stacks the columns. I have not been able to prevent this. I won't go
into how frustrating this has been, let's just stick to a solution.

 

[Bernard Liengme]

Look here and all your worries are over
http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html

 

[scott]

i appreciate "work around", but that is no substitute for a product that
functions properly and allows for one to remove programed assumptions.
however, i still would like for this work, so until the "fix this" (not
holding breath), let's get your suggestion to work.

now, i am using excel 2007 and many of the options here do not seem to work.
i am receiving the error message "the references is not valid. references
for titles, values, or sizes must be a single cell, row, or column".

 

[Jon Peltier]

This does not fit high on my list of program defects. Each axis group is
being plotted appropriately to the category axis it is using. The user
sometimes has to make adjustments to make combinations work out differently
than they are programmed.

- Jon

 

[scott]

may i please have a response from someone other than jon, he might be too
busy working on higher priority items.

here is the last issue i am having with the original post. the width of my
column bars are very inconsistent.

i have 1 set of data on the primary axis, 1 set of data on the secondary
axis. 2 sets of dummy data on the secondary axis. now, the column widths are
close, but not close enough, and there is still some overlap (again not a
lot, but enough that needs to be corrected). also, i have looking to see if
there is way to move each column to right, left, or center justified similar
to how the axis labels can be manipulated (jon add this down on your lowest
priority if you would please). is there a way to fix this spacial, overlap,
right/left justification issue with the columns?

 

[Jon Peltier]

It sounds like you didn't make the right number of series. You need the same
number of series on each axis; you need as many dummy primary series as you
have non-dummy secondary series, and as many dummy secondary series as you
have non-dummy primary series. Each dummy series on one axis lines up with a
non-dummy series on the other axis. The gap width for the column charts in
each axis group should be the same.

- Jon

 

[scott]

Yes, that did finally do it, but the point that needs to be emphasized is
that the order the data is added to the graph, first the primary vertical
axis real data, second primary vertical axis dummy data, third secondary
vertical axis dummy data, and fourth secondary vertical axis real data.

Thanks for the help.

 

[Jon Peltier]

You can change the order either in the Series Order tab of the Format Series
dialog, or by changing the last number in the series formula.

- Jon

 

[GR]

How do I remove the dummy legend entries in Excel 2007? I can't seem to find
any option to do this.

 

[Jon Peltier]

Click once to select the legend, then click again on the legend entry, then
press Delete.

- Jon