Two Bar Graphs with Two Different Y Axes

[OilEconomist]

How can I graph two lines of data into bars that share the same x axis, but different Y axes and are not stacked. The alternate is to make one of them a line graph, but in the case I need them both as independent bar graphs next to each other and referencing different y axes.

Thanks in advance and I will give feedback to those who assist on whether any proposed solutions work.

 

[Gerald Higgins]

Plot one set of bars on the left Y axis, and the other set on the right Y axis.

Set up your chart as normal, then right click on the series that you want to plot on the right Y axis.
Select "Format Data Series".
Then on the Series Options screen, tick the option to "Plot Series on" . . . "Secondary Axis"
And that should be it.

If you choose to do this, that second series can be another set of bars, or a line, it's up to you.
You can also do this for lots of series - for example if you have 10 sets of bars, 5 can be plotted against left axis, and 5 against right, but it might get confusing !

 

[OilEconomist]

Thanks for your assistance, but what this does is stack the bar charts. I'm trying to figure out a way where it does not do that.

Try it for yourself and if you have success, please let me know how.


Thanks for the assistance you did provide thus far.

 

[Gerald Higgins]

So it does, I wasn't expecting that !

The only way I can think of fixing this is to reformat your source data a little.

So if you are starting with data like this . . .

..............Col A........Col B..........Col C
Row 1.....X Axis........Series 1......Series 2
Row 2......A.............1...............5
Row 3......B.............2...............4
Row 4......C.............3...............3
Row 5......D.............4...............2
Row 6......E.............5...............1

Reformat it to something like this . . .
..............Col A........Col B..........Col C
Row 1.....X Axis........Series 1......Series 2
Row 2......A.............1...............
Row 3.....................................5
Row 4......B.............2................
Row 5.....................................4
Row 6......C.............3................
Row 7.....................................3
Row 8......D.............4................
Row 9.....................................2
Row 10.....E.............5................
Row 11....................................1

See the difference ?
I've tested this and it does work.

If you don't like the X axis labels being too closely associated with one or other of the data series, try adding more intermediate rows to re-position the labels.

 

[OilEconomist]

Thanks so much for finding a solution as that works.

Now I want to see if I or anyone else is able to figure it out without adding the blank rows. Let me know if you have any luck.

Once again thanks so much for finding me a workaround for now.