PC Review


Reply
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average.

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

 
 
griff
Guest
Posts: n/a
 
      5th Dec 2007
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
 
Reply With Quote
 
 
 
 
Jon Peltier
Guest
Posts: n/a
 
      5th Dec 2007
This link shows how to handle this with column charts. For bar charts the
approach is very similar.

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

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


"griff" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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



 
Reply With Quote
 
 
 
 
griff
Guest
Posts: n/a
 
      5th Dec 2007
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/...OnTwoAxes.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "griff" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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

>
>
>

 
Reply With Quote
 
Del Cotter
Guest
Posts: n/a
 
      5th Dec 2007
On Wed, 5 Dec 2007, in microsoft.public.excel.charting,
griff <(E-Mail Removed)> 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/...OnTwoAxes.html

--
Del Cotter
NB Personal replies to this post will send email to (E-Mail Removed),
which goes to a spam folder-- please send your email to del3 instead.
 
Reply With Quote
 
griff
Guest
Posts: n/a
 
      5th Dec 2007

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/...OnTwoAxes.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "griff" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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

>
>
>

 
Reply With Quote
 
griff
Guest
Posts: n/a
 
      5th Dec 2007
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 <(E-Mail Removed)> 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/...OnTwoAxes.html
>
> --
> Del Cotter
> NB Personal replies to this post will send email to (E-Mail Removed),
> which goes to a spam folder-- please send your email to del3 instead.
>

 
Reply With Quote
 
Del Cotter
Guest
Posts: n/a
 
      5th Dec 2007
On Wed, 5 Dec 2007, in microsoft.public.excel.charting,
griff <(E-Mail Removed)> 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 (E-Mail Removed),
which goes to a spam folder-- please send your email to del3 instead.
 
Reply With Quote
 
Jon Peltier
Guest
Posts: n/a
 
      5th Dec 2007
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" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> 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 <(E-Mail Removed)> 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/...OnTwoAxes.html
>>
>> --
>> Del Cotter
>> NB Personal replies to this post will send email to
>> (E-Mail Removed),
>> which goes to a spam folder-- please send your email to del3 instead.
>>



 
Reply With Quote
 
griff
Guest
Posts: n/a
 
      6th Dec 2007
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" <(E-Mail Removed)> wrote in message
> news(E-Mail Removed)...
> > 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 <(E-Mail Removed)> 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/...OnTwoAxes.html
> >>
> >> --
> >> Del Cotter
> >> NB Personal replies to this post will send email to
> >> (E-Mail Removed),
> >> which goes to a spam folder-- please send your email to del3 instead.
> >>

>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Overlapping time (X) axis labels with auto axis options Paul Kraemer Microsoft Excel Charting 1 8th Feb 2009 04:44 AM
make chart with both horizontal-x axis vertical-y axis CEmery Microsoft Excel Charting 1 19th Dec 2007 08:11 PM
How to place a secondary y axis bar in a bar chart next to two primary y axis bars? Dmitry Microsoft Excel Discussion 1 4th May 2006 01:58 AM
How to place a secondary y axis bar in a bar chart next to two primary y axis bars? Dmitry Microsoft Excel Misc 3 4th May 2006 01:58 AM
How to place a secondary y axis bar in a bar chart next to two primary y axis bars? Dmitry Microsoft Excel Charting 2 4th May 2006 01:58 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:49 PM.