Multiple columns in stack graph

[Guest]

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

 

[Guest]

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.

 

[Guest]

Hi,

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

Amanda

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thats great - exactly what I was looking for.

Thanks

Amanda

 

[sachinattri]

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

 

[Jon Peltier]

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