Thread: Multiple columns in stack graph

Hi,

I have four pieces of data which I wish to stack in two columns, I can only
manage to create a graph with one colum containing all four pieces of data.

For example:
North
Rep1 - jan05 - feb05 - mar05 - etc
Rep2 - jan05 - feb05 - mar05 - etc

South
Rep3 - jan05 - feb05 - mar05 - etc
Rep4 - jan05 - feb05 - mar05 - etc

By month, I would like to see two columns, one containing north and one
containing south. (North is a stack of reps 1 & 2, south of reps 3 & 4).

I hope this makes sense, and someone can help me.

Thank you

Amanda

Set up your data such that there are three rows (or columns) per month
have one row be your north, one be south and one be blank.
make a stacked column graph and set the width of the columns to 100 percent.


"Amanda" wrote:

> Hi,
>
> I have four pieces of data which I wish to stack in two columns, I can only
> manage to create a graph with one colum containing all four pieces of data.
>
> For example:
> North
> Rep1 - jan05 - feb05 - mar05 - etc
> Rep2 - jan05 - feb05 - mar05 - etc
>
> South
> Rep3 - jan05 - feb05 - mar05 - etc
> Rep4 - jan05 - feb05 - mar05 - etc
>
> By month, I would like to see two columns, one containing north and one
> containing south. (North is a stack of reps 1 & 2, south of reps 3 & 4).
>
> I hope this makes sense, and someone can help me.
>
> Thank you
>
> Amanda
>
>

Hi,

This doesn't allow me to show the four reps data? It stacks north and south
twice, or have I misunderstood?

Amanda

"bj" wrote:

> Set up your data such that there are three rows (or columns) per month
> have one row be your north, one be south and one be blank.
> make a stacked column graph and set the width of the columns to 100 percent.
>
>
> "Amanda" wrote:
>
> > Hi,
> >
> > I have four pieces of data which I wish to stack in two columns, I can only
> > manage to create a graph with one colum containing all four pieces of data.
> >
> > For example:
> > North
> > Rep1 - jan05 - feb05 - mar05 - etc
> > Rep2 - jan05 - feb05 - mar05 - etc
> >
> > South
> > Rep3 - jan05 - feb05 - mar05 - etc
> > Rep4 - jan05 - feb05 - mar05 - etc
> >
> > By month, I would like to see two columns, one containing north and one
> > containing south. (North is a stack of reps 1 & 2, south of reps 3 & 4).
> >
> > I hope this makes sense, and someone can help me.
> >
> > Thank you
> >
> > Amanda
> >
> >

example
A1="Month"
B1= ""
C1= "Rep1/3"
D1= "Rep2/4"
A2="Jan"
B2="North"
C2=rep1 value (Jan)
D2=Rep2 value (Jan)
A3="Jan"
B3="South"
C3=Rep3 value (Jan)
D3=Rep4 value (Jan)
A5="Feb"
B5="North"
C5=rep1 value (Feb)
D5=Rep2 value (Feb)
A6="Jan"
B6="South"
C6=Rep3 value (Feb)
D3=Rep4 value (Feb)
etc.
This is four columns rather than 3 like I said before, but I added the
north and south designators.
Plot columns C and D as stacked chart and set the Width of the columns as
one hundred %
go to chart_source data_ series and in Catagorie X axis lables enter A2:B37
(or however low you need to go


"Amanda" wrote:

> Hi,
>
> This doesn't allow me to show the four reps data? It stacks north and south
> twice, or have I misunderstood?
>
> Amanda
>
> "bj" wrote:
>
> > Set up your data such that there are three rows (or columns) per month
> > have one row be your north, one be south and one be blank.
> > make a stacked column graph and set the width of the columns to 100 percent.
> >
> >
> > "Amanda" wrote:
> >
> > > Hi,
> > >
> > > I have four pieces of data which I wish to stack in two columns, I can only
> > > manage to create a graph with one colum containing all four pieces of data.
> > >
> > > For example:
> > > North
> > > Rep1 - jan05 - feb05 - mar05 - etc
> > > Rep2 - jan05 - feb05 - mar05 - etc
> > >
> > > South
> > > Rep3 - jan05 - feb05 - mar05 - etc
> > > Rep4 - jan05 - feb05 - mar05 - etc
> > >
> > > By month, I would like to see two columns, one containing north and one
> > > containing south. (North is a stack of reps 1 & 2, south of reps 3 & 4).
> > >
> > > I hope this makes sense, and someone can help me.
> > >
> > > Thank you
> > >
> > > Amanda
> > >
> > >

It might be better to set up the data like this, in six columns:

Rep 1 Rep 2 Rep 3 Rep 4
Jan North 7 8
South 3 3
Feb North 5 3
South 5 8
Mar North 8 6
South 7 6

(read the above in non proportional font like Courier). All apparently empty cells
must be blank. Select the entire range, including two blanks in top left, and make
your stacked column chart. The blanks tell Excel to use Rep 1 through Rep 4 as
series names and the month and region columns as a two-row category label. The
different reps are each in their own series, so formatting individually is much easier.

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

bj wrote:

> example
> A1="Month"
> B1= ""
> C1= "Rep1/3"
> D1= "Rep2/4"
> A2="Jan"
> B2="North"
> C2=rep1 value (Jan)
> D2=Rep2 value (Jan)
> A3="Jan"
> B3="South"
> C3=Rep3 value (Jan)
> D3=Rep4 value (Jan)
> A5="Feb"
> B5="North"
> C5=rep1 value (Feb)
> D5=Rep2 value (Feb)
> A6="Jan"
> B6="South"
> C6=Rep3 value (Feb)
> D3=Rep4 value (Feb)
> etc.
> This is four columns rather than 3 like I said before, but I added the
> north and south designators.
> Plot columns C and D as stacked chart and set the Width of the columns as
> one hundred %
> go to chart_source data_ series and in Catagorie X axis lables enter A2:B37
> (or however low you need to go
>
>
> "Amanda" wrote:
>
>
>>Hi,
>>
>>This doesn't allow me to show the four reps data? It stacks north and south
>>twice, or have I misunderstood?
>>
>>Amanda
>>
>>"bj" wrote:
>>
>>
>>>Set up your data such that there are three rows (or columns) per month
>>>have one row be your north, one be south and one be blank.
>>>make a stacked column graph and set the width of the columns to 100 percent.
>>>
>>>
>>>"Amanda" wrote:
>>>
>>>
>>>>Hi,
>>>>
>>>>I have four pieces of data which I wish to stack in two columns, I can only
>>>>manage to create a graph with one colum containing all four pieces of data.
>>>>
>>>>For example:
>>>>North
>>>>Rep1 - jan05 - feb05 - mar05 - etc
>>>>Rep2 - jan05 - feb05 - mar05 - etc
>>>>
>>>>South
>>>>Rep3 - jan05 - feb05 - mar05 - etc
>>>>Rep4 - jan05 - feb05 - mar05 - etc
>>>>
>>>>By month, I would like to see two columns, one containing north and one
>>>>containing south. (North is a stack of reps 1 & 2, south of reps 3 & 4).
>>>>
>>>>I hope this makes sense, and someone can help me.
>>>>
>>>>Thank you
>>>>
>>>>Amanda
>>>>
>>>>

I like Jon's method

"Jon Peltier" wrote:

> It might be better to set up the data like this, in six columns:
>
> Rep 1 Rep 2 Rep 3 Rep 4
> Jan North 7 8
> South 3 3
> Feb North 5 3
> South 5 8
> Mar North 8 6
> South 7 6
>
> (read the above in non proportional font like Courier). All apparently empty cells
> must be blank. Select the entire range, including two blanks in top left, and make
> your stacked column chart. The blanks tell Excel to use Rep 1 through Rep 4 as
> series names and the month and region columns as a two-row category label. The
> different reps are each in their own series, so formatting individually is much easier.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> bj wrote:
>
> > example
> > A1="Month"
> > B1= ""
> > C1= "Rep1/3"
> > D1= "Rep2/4"
> > A2="Jan"
> > B2="North"
> > C2=rep1 value (Jan)
> > D2=Rep2 value (Jan)
> > A3="Jan"
> > B3="South"
> > C3=Rep3 value (Jan)
> > D3=Rep4 value (Jan)
> > A5="Feb"
> > B5="North"
> > C5=rep1 value (Feb)
> > D5=Rep2 value (Feb)
> > A6="Jan"
> > B6="South"
> > C6=Rep3 value (Feb)
> > D3=Rep4 value (Feb)
> > etc.
> > This is four columns rather than 3 like I said before, but I added the
> > north and south designators.
> > Plot columns C and D as stacked chart and set the Width of the columns as
> > one hundred %
> > go to chart_source data_ series and in Catagorie X axis lables enter A2:B37
> > (or however low you need to go
> >
> >
> > "Amanda" wrote:
> >
> >
> >>Hi,
> >>
> >>This doesn't allow me to show the four reps data? It stacks north and south
> >>twice, or have I misunderstood?
> >>
> >>Amanda
> >>
> >>"bj" wrote:
> >>
> >>
> >>>Set up your data such that there are three rows (or columns) per month
> >>>have one row be your north, one be south and one be blank.
> >>>make a stacked column graph and set the width of the columns to 100 percent.
> >>>
> >>>
> >>>"Amanda" wrote:
> >>>
> >>>
> >>>>Hi,
> >>>>
> >>>>I have four pieces of data which I wish to stack in two columns, I can only
> >>>>manage to create a graph with one colum containing all four pieces of data.
> >>>>
> >>>>For example:
> >>>>North
> >>>>Rep1 - jan05 - feb05 - mar05 - etc
> >>>>Rep2 - jan05 - feb05 - mar05 - etc
> >>>>
> >>>>South
> >>>>Rep3 - jan05 - feb05 - mar05 - etc
> >>>>Rep4 - jan05 - feb05 - mar05 - etc
> >>>>
> >>>>By month, I would like to see two columns, one containing north and one
> >>>>containing south. (North is a stack of reps 1 & 2, south of reps 3 & 4).
> >>>>
> >>>>I hope this makes sense, and someone can help me.
> >>>>
> >>>>Thank you
> >>>>
> >>>>Amanda
> >>>>
> >>>>
>
>

Thats great - exactly what I was looking for.

Thanks

Amanda

"Jon Peltier" wrote:

> It might be better to set up the data like this, in six columns:
>
> Rep 1 Rep 2 Rep 3 Rep 4
> Jan North 7 8
> South 3 3
> Feb North 5 3
> South 5 8
> Mar North 8 6
> South 7 6
>
> (read the above in non proportional font like Courier). All apparently empty cells
> must be blank. Select the entire range, including two blanks in top left, and make
> your stacked column chart. The blanks tell Excel to use Rep 1 through Rep 4 as
> series names and the month and region columns as a two-row category label. The
> different reps are each in their own series, so formatting individually is much easier.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> bj wrote:
>
> > example
> > A1="Month"
> > B1= ""
> > C1= "Rep1/3"
> > D1= "Rep2/4"
> > A2="Jan"
> > B2="North"
> > C2=rep1 value (Jan)
> > D2=Rep2 value (Jan)
> > A3="Jan"
> > B3="South"
> > C3=Rep3 value (Jan)
> > D3=Rep4 value (Jan)
> > A5="Feb"
> > B5="North"
> > C5=rep1 value (Feb)
> > D5=Rep2 value (Feb)
> > A6="Jan"
> > B6="South"
> > C6=Rep3 value (Feb)
> > D3=Rep4 value (Feb)
> > etc.
> > This is four columns rather than 3 like I said before, but I added the
> > north and south designators.
> > Plot columns C and D as stacked chart and set the Width of the columns as
> > one hundred %
> > go to chart_source data_ series and in Catagorie X axis lables enter A2:B37
> > (or however low you need to go
> >
> >
> > "Amanda" wrote:
> >
> >
> >>Hi,
> >>
> >>This doesn't allow me to show the four reps data? It stacks north and south
> >>twice, or have I misunderstood?
> >>
> >>Amanda
> >>
> >>"bj" wrote:
> >>
> >>
> >>>Set up your data such that there are three rows (or columns) per month
> >>>have one row be your north, one be south and one be blank.
> >>>make a stacked column graph and set the width of the columns to 100 percent.
> >>>
> >>>
> >>>"Amanda" wrote:
> >>>
> >>>
> >>>>Hi,
> >>>>
> >>>>I have four pieces of data which I wish to stack in two columns, I can only
> >>>>manage to create a graph with one colum containing all four pieces of data.
> >>>>
> >>>>For example:
> >>>>North
> >>>>Rep1 - jan05 - feb05 - mar05 - etc
> >>>>Rep2 - jan05 - feb05 - mar05 - etc
> >>>>
> >>>>South
> >>>>Rep3 - jan05 - feb05 - mar05 - etc
> >>>>Rep4 - jan05 - feb05 - mar05 - etc
> >>>>
> >>>>By month, I would like to see two columns, one containing north and one
> >>>>containing south. (North is a stack of reps 1 & 2, south of reps 3 & 4).
> >>>>
> >>>>I hope this makes sense, and someone can help me.
> >>>>
> >>>>Thank you
> >>>>
> >>>>Amanda
> >>>>
> >>>>
>
>

Set up your data like this:

A-Voice B-Voice A-Email B-Email
x
April 10 10
8 12
x
May 11 9
9 11
x
June 12 8
10 10
x

The rows with X in the first column have no values in the last four
columns, and you should replace the X with a space (not a blank cell).
Select the range and make a stacked column chart. Double click one of
the columns, and on the Options tab, change Gap Width to zero. The blank
rows provide gaps between certain pairs of stacks.

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


sachinattri wrote:
> I dont understand this. Sorry to be ignorant. I have the data under
> following columns
>
> Country A Voice - Country B Voice - Country A Email - Country B Email
>
>
> and then these 4 columns headings have numbers under them By Month.
> let's say for Apr-May-Jun
>
> How do I get 2 stack columns side by side, showing Country A Voice &
> Country B Voice and the second stack next to it for Country A Email &
> Country B Email for April, then May and then June.
>
> Thanks
>
> /sa
>
>