Dual Axis Horizontal Bar Chart in Excel-can I avoid overlapping ba

G

griff

hello,

I have a simple table of data with 2 columns of numbers, which I'm trying to
build into a simple horizontal bar chart

Problem is that the 1 column's numbers are much greater than the other, so
this means that makes the smaller series' bars look very small. I would like
to see both displayed together however, but if I put it on a secondary axis
it overlays one on top of the other, rather than side-by-side

Is there an easy way to stop this happening? any help would be greatly
appreciated

cheers

griff
 
G

griff

thanks jon - was hoping there was an options button that i was missing, but i
should have known better!

cheers!

griff
 
D

Del Cotter

I have a simple table of data with 2 columns of numbers, which I'm trying to
build into a simple horizontal bar chart
but if I put it on a secondary axis it overlays one on top of the
other, rather than side-by-side

Is there an easy way to stop this happening? any help would be greatly
appreciated

The best way is to create a pair of phantom bars, one on each axis.
They're both zero (or formatted to be invisible), and each one
"overlays" the bar that you want to display on the other axis. But
because they're zero or invisible, they're not overlaying or covering
anything, so you get the effect you want, which is your two actual bars
lying side-by-side.

Jon Peltier shows how to do it here:

http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html
 
G

griff

forgot to mention that one of the sets of numbers has a couple of negative
values - had some great fun manually tweaking the scale to get them to match!
 
G

griff

thanks del, but i'm afraid jon p already beat you to the same answer!
if however you can find a way to avoid manually messing around with scales
to make the x axes align when you have negative values then that would be
even better!

cheers

griff
 
D

Del Cotter

thanks del, but i'm afraid jon p already beat you to the same answer!
if however you can find a way to avoid manually messing around with scales
to make the x axes align when you have negative values then that would be
even better!

Well, because you've created an invisible bar in each axis, you have a
way of making the two scales *automatically* match, no matter what data
you put in. Provided, that is, that you went the route of formatting
the bars to be "Border=None, Area=None" instead of having them contain
zero values.

Just have each invisible bar series be some scaled multiple (or
fraction) of the same values as its visible doppelganger, and the scales
will never mismatch, even when left on automatic.

PS this works if the multiple in question is a power of ten, or five
times a power of ten. For other scalings the zeroes sometimes end up
misaligned.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top