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

To change the gap width between specific columns in Excel, follow these steps:

1. Select the chart that contains the columns you want to adjust.
2. Right-click on one of the columns you want to adjust and select "Format Data Series" from the drop-down menu.
3. In the "Format Data Series" pane that appears on the right-hand side of the screen, click on the "Series Options" tab.
4. Under "Series Options", you should see a section called "Gap Width". By default, this is set to
   Formula:

   "150%" 

   . You can adjust this percentage to increase or decrease the gap width between columns.
5. To adjust the gap width between specific columns, you can use the "Overlap" option. This option allows you to shift the position of each column relative to the others. For example, if you set the overlap to
   Formula:

   "100%" 

   , the columns will overlap each other completely. If you set the overlap to
   Formula:

   "-100%" 

   , the columns will be completely separate from each other.
6. To adjust the overlap for specific columns, you can use the "Series Overlap" option. This option allows you to adjust the overlap for each individual series in your chart. For example, if you have two data sets for each year, you can adjust the overlap for each set separately to achieve the desired effect.

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

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

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

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

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

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

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.