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

Discussion in 'Microsoft Excel Charting' started by griff, Dec 5, 2007.

  1. griff

    griff Guest

    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
     
    griff, Dec 5, 2007
    #1
    1. Advertisements

  2. griff

    Jon Peltier Guest

    This link shows how to handle this with column charts. For bar charts the
    approach is very similar.

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

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Tutorials and Custom Solutions
    Peltier Technical Services, Inc. - http://PeltierTech.com
    _______


    "griff" <> wrote in message
    news:...
    > 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
     
    Jon Peltier, Dec 5, 2007
    #2
    1. Advertisements

  3. griff

    griff Guest

    Re: Dual Axis Horizontal Bar Chart in Excel-can I avoid overlappin

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

    cheers!

    griff

    "Jon Peltier" wrote:

    > This link shows how to handle this with column charts. For bar charts the
    > approach is very similar.
    >
    > http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Tutorials and Custom Solutions
    > Peltier Technical Services, Inc. - http://PeltierTech.com
    > _______
    >
    >
    > "griff" <> wrote in message
    > news:...
    > > 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

    >
    >
    >
     
    griff, Dec 5, 2007
    #3
  4. griff

    Del Cotter Guest

    On Wed, 5 Dec 2007, in microsoft.public.excel.charting,
    griff <> said:

    >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

    --
    Del Cotter
    NB Personal replies to this post will send email to ,
    which goes to a spam folder-- please send your email to del3 instead.
     
    Del Cotter, Dec 5, 2007
    #4
  5. griff

    griff Guest

    Re: Dual Axis Horizontal Bar Chart in Excel-can I avoid overlappin

    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!


    "Jon Peltier" wrote:

    > This link shows how to handle this with column charts. For bar charts the
    > approach is very similar.
    >
    > http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Tutorials and Custom Solutions
    > Peltier Technical Services, Inc. - http://PeltierTech.com
    > _______
    >
    >
    > "griff" <> wrote in message
    > news:...
    > > 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

    >
    >
    >
     
    griff, Dec 5, 2007
    #5
  6. griff

    griff Guest

    Re: Dual Axis Horizontal Bar Chart in Excel-can I avoid overlappin

    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

    "Del Cotter" wrote:

    > On Wed, 5 Dec 2007, in microsoft.public.excel.charting,
    > griff <> said:
    >
    > >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
    >
    > --
    > Del Cotter
    > NB Personal replies to this post will send email to ,
    > which goes to a spam folder-- please send your email to del3 instead.
    >
     
    griff, Dec 5, 2007
    #6
  7. griff

    Del Cotter Guest

    Re: Dual Axis Horizontal Bar Chart in Excel-can I avoid overlappin

    On Wed, 5 Dec 2007, in microsoft.public.excel.charting,
    griff <> said:

    >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.

    --
    Del Cotter
    NB Personal replies to this post will send email to ,
    which goes to a spam folder-- please send your email to del3 instead.
     
    Del Cotter, Dec 5, 2007
    #7
  8. griff

    Jon Peltier Guest

    Re: Dual Axis Horizontal Bar Chart in Excel-can I avoid overlappin

    Griff -

    How's this:

    http://peltiertech.com/Excel/Charts/AlignXon2Ys.html

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Tutorials and Custom Solutions
    Peltier Technical Services, Inc. - http://PeltierTech.com
    _______


    "griff" <> wrote in message
    news:D...
    > 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
    >
    > "Del Cotter" wrote:
    >
    >> On Wed, 5 Dec 2007, in microsoft.public.excel.charting,
    >> griff <> said:
    >>
    >> >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
    >>
    >> --
    >> Del Cotter
    >> NB Personal replies to this post will send email to
    >> ,
    >> which goes to a spam folder-- please send your email to del3 instead.
    >>
     
    Jon Peltier, Dec 5, 2007
    #8
  9. griff

    griff Guest

    Re: Dual Axis Horizontal Bar Chart in Excel-can I avoid overlappin

    Thanks Jon & Del for your advice - most helpful!

    cheers,

    Griff

    "Jon Peltier" wrote:

    > Griff -
    >
    > How's this:
    >
    > http://peltiertech.com/Excel/Charts/AlignXon2Ys.html
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Tutorials and Custom Solutions
    > Peltier Technical Services, Inc. - http://PeltierTech.com
    > _______
    >
    >
    > "griff" <> wrote in message
    > news:D...
    > > 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
    > >
    > > "Del Cotter" wrote:
    > >
    > >> On Wed, 5 Dec 2007, in microsoft.public.excel.charting,
    > >> griff <> said:
    > >>
    > >> >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
    > >>
    > >> --
    > >> Del Cotter
    > >> NB Personal replies to this post will send email to
    > >> ,
    > >> which goes to a spam folder-- please send your email to del3 instead.
    > >>

    >
    >
    >
     
    griff, Dec 6, 2007
    #9
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Dmitry
    Replies:
    2
    Views:
    666
    Jon Peltier
    May 4, 2006
  2. CEmery

    make chart with both horizontal-x axis vertical-y axis

    CEmery, Dec 19, 2007, in forum: Microsoft Excel Charting
    Replies:
    1
    Views:
    484
    Del Cotter
    Dec 19, 2007
  3. Paul Kraemer

    Overlapping time (X) axis labels with auto axis options

    Paul Kraemer, Feb 7, 2009, in forum: Microsoft Excel Charting
    Replies:
    1
    Views:
    1,574
    Jon Peltier
    Feb 8, 2009
  4. Move x axis to top of horz. 3D Bar Chart

    Move x axis to the top of a 3D horizontal bar chart

    Move x axis to top of horz. 3D Bar Chart, Jun 17, 2009, in forum: Microsoft Excel Charting
    Replies:
    1
    Views:
    252
  5. SKSmith

    Emphasize one Line on Horizontal Axis of Bar chart

    SKSmith, Oct 7, 2009, in forum: Microsoft Excel Charting
    Replies:
    5
    Views:
    366
    SKSmith
    Oct 13, 2009
Loading...

Share This Page