Multiple columns in stack graph

[Amanda]

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

 

[bj]

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]

Hi,

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

Amanda

 

[bj]

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

 

[Jon Peltier]

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]

I like Jon's method

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/
_______

 

[Amanda]

Thats great - exactly what I was looking for.

Thanks

Amanda