A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Charts and Charting in Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

changing gap width between specific columns



 
 
Thread Tools Display Modes
  #1  
Old January 23rd 08, 02:25 PM posted to microsoft.public.excel.charting
Noa
external usenet poster
 
Posts: 11
Default changing gap width between specific columns

I have 2 data sets for each year, and would like each two to be presented
without any spacing for each year. The spacing should remain between each
year. Right now I have the same gap between all columns.
Changing the gap width or overlap using the Format data series does not do
the job.

Thanks
Noa
Ads
  #2  
Old January 23rd 08, 02:54 PM posted to microsoft.public.excel.charting
Jon Peltier
external usenet poster
 
Posts: 6,582
Default changing gap width between specific columns

Do you have just one set of data? Set up the data like this:

[blank] Series 1 Series 2
Label 1 Data Data
Label 2 Data Data
Label 3 Data Data

Make a clustered column chart with series in columns. The series 1 and
series 2 columns above each label will be in contact, with a gap between the
data for label 1 and label 2, and between label 2 and label 3.

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


"Noa" > wrote in message
...
>I have 2 data sets for each year, and would like each two to be presented
> without any spacing for each year. The spacing should remain between each
> year. Right now I have the same gap between all columns.
> Changing the gap width or overlap using the Format data series does not do
> the job.
>
> Thanks
> Noa



  #3  
Old January 23rd 08, 04:08 PM posted to microsoft.public.excel.charting
Noa
external usenet poster
 
Posts: 11
Default changing gap width between specific columns

Thanks Jon.
I am using stacked Column chart, and not a Clustered Column chart. I need
Series 1 and Series 2 to be in contact.

Thanks again
Noa

"Jon Peltier" wrote:

> Do you have just one set of data? Set up the data like this:
>
> [blank] Series 1 Series 2
> Label 1 Data Data
> Label 2 Data Data
> Label 3 Data Data
>
> Make a clustered column chart with series in columns. The series 1 and
> series 2 columns above each label will be in contact, with a gap between the
> data for label 1 and label 2, and between label 2 and label 3.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Noa" > wrote in message
> ...
> >I have 2 data sets for each year, and would like each two to be presented
> > without any spacing for each year. The spacing should remain between each
> > year. Right now I have the same gap between all columns.
> > Changing the gap width or overlap using the Format data series does not do
> > the job.
> >
> > Thanks
> > Noa

>
>
>

  #4  
Old January 23rd 08, 08:41 PM posted to microsoft.public.excel.charting
Jon Peltier
external usenet poster
 
Posts: 6,582
Default changing gap width between specific columns

I'm afraid I don't understand the problem. Are Series 1 and 2 stacked? If
so, how can they be in contact, other than to top of one touching the base
of the other?

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


"Noa" > wrote in message
...
> Thanks Jon.
> I am using stacked Column chart, and not a Clustered Column chart. I need
> Series 1 and Series 2 to be in contact.
>
> Thanks again
> Noa
>
> "Jon Peltier" wrote:
>
>> Do you have just one set of data? Set up the data like this:
>>
>> [blank] Series 1 Series 2
>> Label 1 Data Data
>> Label 2 Data Data
>> Label 3 Data Data
>>
>> Make a clustered column chart with series in columns. The series 1 and
>> series 2 columns above each label will be in contact, with a gap between
>> the
>> data for label 1 and label 2, and between label 2 and label 3.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "Noa" > wrote in message
>> ...
>> >I have 2 data sets for each year, and would like each two to be
>> >presented
>> > without any spacing for each year. The spacing should remain between
>> > each
>> > year. Right now I have the same gap between all columns.
>> > Changing the gap width or overlap using the Format data series does not
>> > do
>> > the job.
>> >
>> > Thanks
>> > Noa

>>
>>
>>



  #5  
Old January 24th 08, 01:42 PM posted to microsoft.public.excel.charting
Noa
external usenet poster
 
Posts: 11
Default changing gap width between specific columns

Hi Jon,
Series 1 and 2, each have 2 data sets.
This is how my data looks:

2000 2001 2002
data1 data2 data1 data2 data1 data2
a 50 70 55 70 80 60
b 20 30 15 10 10 20

a+b in each data column are stacked,. Now each column is presented separated
from the other in the chart. I would like the stacked columns (datat1 and
data2) for each year to have no gap between them. I do want to keep the gap
between each year. Is there a way to do that without changing the columns'
width? I know I can add space between columns if I insert an empty column in
the table.

Thanks

"Jon Peltier" wrote:

> I'm afraid I don't understand the problem. Are Series 1 and 2 stacked? If
> so, how can they be in contact, other than to top of one touching the base
> of the other?
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Noa" > wrote in message
> ...
> > Thanks Jon.
> > I am using stacked Column chart, and not a Clustered Column chart. I need
> > Series 1 and Series 2 to be in contact.
> >
> > Thanks again
> > Noa
> >
> > "Jon Peltier" wrote:
> >
> >> Do you have just one set of data? Set up the data like this:
> >>
> >> [blank] Series 1 Series 2
> >> Label 1 Data Data
> >> Label 2 Data Data
> >> Label 3 Data Data
> >>
> >> Make a clustered column chart with series in columns. The series 1 and
> >> series 2 columns above each label will be in contact, with a gap between
> >> the
> >> data for label 1 and label 2, and between label 2 and label 3.
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Tutorials and Custom Solutions
> >> Peltier Technical Services, Inc. - http://PeltierTech.com
> >> _______
> >>
> >>
> >> "Noa" > wrote in message
> >> ...
> >> >I have 2 data sets for each year, and would like each two to be
> >> >presented
> >> > without any spacing for each year. The spacing should remain between
> >> > each
> >> > year. Right now I have the same gap between all columns.
> >> > Changing the gap width or overlap using the Format data series does not
> >> > do
> >> > the job.
> >> >
> >> > Thanks
> >> > Noa
> >>
> >>
> >>

>
>
>

  #6  
Old January 24th 08, 02:21 PM posted to microsoft.public.excel.charting
Jon Peltier
external usenet poster
 
Posts: 6,582
Default changing gap width between specific columns

You said it yourself:

> I know I can add space between columns if I insert an empty column in
> the table.


If you have a reason you don't want your display data cluttered by empty
spaces, then copy the display regions, go to an empty worksheet or an out of
the way region of this sheet, and use Paste Special Links. Then insert blank
columns where needed and make your chart from this new range.

Worksheets are cheap, extra ranges do not cost much in this day of 200 GB
hard drives. Your time spent trying to get the chart the way you want
without using this abundant natural resource amounted to hours. I always
tell people, spending five minutes with the data will save five hours of
frustration (or is it ten minutes and ten hours?).

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


"Noa" > wrote in message
...
> Hi Jon,
> Series 1 and 2, each have 2 data sets.
> This is how my data looks:
>
> 2000 2001 2002
> data1 data2 data1 data2 data1 data2
> a 50 70 55 70 80 60
> b 20 30 15 10 10 20
>
> a+b in each data column are stacked,. Now each column is presented
> separated
> from the other in the chart. I would like the stacked columns (datat1 and
> data2) for each year to have no gap between them. I do want to keep the
> gap
> between each year. Is there a way to do that without changing the columns'
> width? I know I can add space between columns if I insert an empty column
> in
> the table.
>
> Thanks
>
> "Jon Peltier" wrote:
>
>> I'm afraid I don't understand the problem. Are Series 1 and 2 stacked? If
>> so, how can they be in contact, other than to top of one touching the
>> base
>> of the other?
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "Noa" > wrote in message
>> ...
>> > Thanks Jon.
>> > I am using stacked Column chart, and not a Clustered Column chart. I
>> > need
>> > Series 1 and Series 2 to be in contact.
>> >
>> > Thanks again
>> > Noa
>> >
>> > "Jon Peltier" wrote:
>> >
>> >> Do you have just one set of data? Set up the data like this:
>> >>
>> >> [blank] Series 1 Series 2
>> >> Label 1 Data Data
>> >> Label 2 Data Data
>> >> Label 3 Data Data
>> >>
>> >> Make a clustered column chart with series in columns. The series 1 and
>> >> series 2 columns above each label will be in contact, with a gap
>> >> between
>> >> the
>> >> data for label 1 and label 2, and between label 2 and label 3.
>> >>
>> >> - Jon
>> >> -------
>> >> Jon Peltier, Microsoft Excel MVP
>> >> Tutorials and Custom Solutions
>> >> Peltier Technical Services, Inc. - http://PeltierTech.com
>> >> _______
>> >>
>> >>
>> >> "Noa" > wrote in message
>> >> ...
>> >> >I have 2 data sets for each year, and would like each two to be
>> >> >presented
>> >> > without any spacing for each year. The spacing should remain between
>> >> > each
>> >> > year. Right now I have the same gap between all columns.
>> >> > Changing the gap width or overlap using the Format data series does
>> >> > not
>> >> > do
>> >> > the job.
>> >> >
>> >> > Thanks
>> >> > Noa
>> >>
>> >>
>> >>

>>
>>
>>



  #7  
Old January 24th 08, 02:43 PM posted to microsoft.public.excel.charting
Noa
external usenet poster
 
Posts: 11
Default changing gap width between specific columns

Hi Jon,
I understand what you mean. We found a way to add gaps between columns but
we didn't find a way to remove gaps between data1 and data2 columns (there is
no empty column between them in the table). I wanted the chart to look just
like a clustered chart would have looked (with only one row for each data1,
data2 set).
I will leave it this way. It's ok.

Thanks


"Jon Peltier" wrote:

> You said it yourself:
>
> > I know I can add space between columns if I insert an empty column in
> > the table.

>
> If you have a reason you don't want your display data cluttered by empty
> spaces, then copy the display regions, go to an empty worksheet or an out of
> the way region of this sheet, and use Paste Special Links. Then insert blank
> columns where needed and make your chart from this new range.
>
> Worksheets are cheap, extra ranges do not cost much in this day of 200 GB
> hard drives. Your time spent trying to get the chart the way you want
> without using this abundant natural resource amounted to hours. I always
> tell people, spending five minutes with the data will save five hours of
> frustration (or is it ten minutes and ten hours?).
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Noa" > wrote in message
> ...
> > Hi Jon,
> > Series 1 and 2, each have 2 data sets.
> > This is how my data looks:
> >
> > 2000 2001 2002
> > data1 data2 data1 data2 data1 data2
> > a 50 70 55 70 80 60
> > b 20 30 15 10 10 20
> >
> > a+b in each data column are stacked,. Now each column is presented
> > separated
> > from the other in the chart. I would like the stacked columns (datat1 and
> > data2) for each year to have no gap between them. I do want to keep the
> > gap
> > between each year. Is there a way to do that without changing the columns'
> > width? I know I can add space between columns if I insert an empty column
> > in
> > the table.
> >
> > Thanks
> >
> > "Jon Peltier" wrote:
> >
> >> I'm afraid I don't understand the problem. Are Series 1 and 2 stacked? If
> >> so, how can they be in contact, other than to top of one touching the
> >> base
> >> of the other?
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Tutorials and Custom Solutions
> >> Peltier Technical Services, Inc. - http://PeltierTech.com
> >> _______
> >>
> >>
> >> "Noa" > wrote in message
> >> ...
> >> > Thanks Jon.
> >> > I am using stacked Column chart, and not a Clustered Column chart. I
> >> > need
> >> > Series 1 and Series 2 to be in contact.
> >> >
> >> > Thanks again
> >> > Noa
> >> >
> >> > "Jon Peltier" wrote:
> >> >
> >> >> Do you have just one set of data? Set up the data like this:
> >> >>
> >> >> [blank] Series 1 Series 2
> >> >> Label 1 Data Data
> >> >> Label 2 Data Data
> >> >> Label 3 Data Data
> >> >>
> >> >> Make a clustered column chart with series in columns. The series 1 and
> >> >> series 2 columns above each label will be in contact, with a gap
> >> >> between
> >> >> the
> >> >> data for label 1 and label 2, and between label 2 and label 3.
> >> >>
> >> >> - Jon
> >> >> -------
> >> >> Jon Peltier, Microsoft Excel MVP
> >> >> Tutorials and Custom Solutions
> >> >> Peltier Technical Services, Inc. - http://PeltierTech.com
> >> >> _______
> >> >>
> >> >>
> >> >> "Noa" > wrote in message
> >> >> ...
> >> >> >I have 2 data sets for each year, and would like each two to be
> >> >> >presented
> >> >> > without any spacing for each year. The spacing should remain between
> >> >> > each
> >> >> > year. Right now I have the same gap between all columns.
> >> >> > Changing the gap width or overlap using the Format data series does
> >> >> > not
> >> >> > do
> >> >> > the job.
> >> >> >
> >> >> > Thanks
> >> >> > Noa
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>

>
>
>

  #8  
Old January 24th 08, 05:56 PM posted to microsoft.public.excel.charting
Del Cotter
external usenet poster
 
Posts: 560
Default changing gap width between specific columns

On Thu, 24 Jan 2008, in microsoft.public.excel.charting,
Noa > said:
>This is how my data looks:
>
> 2000 2001 2002
> data1 data2 data1 data2 data1 data2
>a 50 70 55 70 80 60
>b 20 30 15 10 10 20
>
>a+b in each data column are stacked,. Now each column is presented separated
>from the other in the chart. I would like the stacked columns (datat1 and
>data2) for each year to have no gap between them. I do want to keep the gap
>between each year. Is there a way to do that without changing the columns'
>width? I know I can add space between columns if I insert an empty column in
>the table.


There is a way, but it's a bit silly. Arrange your data table like so:

2000 2001 2002
data1 a 50 55 80 (move to secondary axis)
b 70 70 90 (contains a+b)
data2 a 70 70 60 (move to secondary axis)
b 100 80 80 (contains a+b)

Note that the "b" column now contains, not the b values alone, but the
sum of a values and b values. Now create a clustered (not stacked)
column chart, selecting rows, not columns. Then move, first the a for
data1, then the a for data2 to the secondary axis. Be careful to set
your primary and secondary axes so they match in scale height.

The b columns, which contain the values a+b, should now line up neatly
with the a columns in front of them, giving the appearance of a stacked
column chart. Practically speaking, though, you're better off just
putting empty spreadsheet columns in your original data tables to get
the effect of separate columns by year.

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




Thread Tools
Display Modes

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

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing the width of the legend key Gerry Charts and Charting in Excel 1 February 19th 07 04:14 AM
change the width of a specific cell Andrew Excel Worksheet Functions 3 October 14th 06 12:19 AM
How do I change the width a specific cell w/o changing whole page THBCFLO Excel Discussion (Misc queries) 3 February 7th 06 11:01 PM
changing thwe width of data labels? Howard Charts and Charting in Excel 2 July 12th 05 07:26 AM
Changing the Width of Bars in a Horizontal bar Graph Ott1 Charts and Charting in Excel 2 June 22nd 05 08:45 PM


All times are GMT +1. The time now is 04:50 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.