Multiple Stacked Bars

[RJB]

Here is my data set:

2007 East 2007 West 2006 East 2006 West
Sid a b c
d
Nancy e f g
h
Johnny i j k
L
Salesman n n1 n2 n3
n4

I want to have a stacked chart with Salesmen 1-n on the X-axis.

And a bar for 2007 and a bar for 2006 for each. I can do it for one year,
and I can do it for all years if I combine East and West, but I can't figure
out how to do both!


So something that looks like this:

|
|
|
|
| b d f h j L
|____ a__c____e__g____i_k
0706 0706 0706
Sid Nancy Johnny

 

[RJB]

OK, so my attempt at making a picture failed miserably.

I want to see:

One bar per year per salesman (so, Sid's 2007 sales, Sid's 2006 sales,
Nancy's 2007 sales, Nancy's 2006 sales).

Each bar I want to show East sales as one color, and West sales as another
color on top of it.

 

[Bob Phillips]

Right-click the chart, and on the Data Range tab, click the Columns button.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[RJB]

What that does is stack ALL sales on one bar.

So I have Sid's 2006 East sales on top of 2006 West sales on top of 2007
East sales on top of 2007 West sales; next to that is Nancy's 2006 East sales
on top of 2006 West sales, etc...

I want, left to right:

Sid's 2007 East sales on top of Sid's 2007 West sales.
Sid's 2006 East sales on top of Sid's 2006 East sales.
Nancy's 2007 East sales on top of Nancy's 2007 West sales.
etc.

Thanks

 

[Andy Pope]

Hi,

Have a look at
http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html

Cheers
Andy

 

[Jon Peltier]

If you compose the text-art in a nonproportional font, it will usually come
out better. Anyway, I think you're looking for this:

http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html

- Jon

 

[RJB]

I only have the one font choice to use. Oh, well.

It appears I have to move data around to get this right, which is totally
uncool as this is actually a much larger spreadsheet (450 clients, six
categories to compare by year, four years; obviously my chart will only
highlight certain clients, but still...).

OK, at least this is a start.

I can't believe there's no "Secondary Series" option!

 

[Jon Peltier]

I only have the one font choice to use. Oh, well.

Outlook Express sometimes doesn't let me use Courier to compose, so I do my
table in Notepad, and paste it into OE.

It appears I have to move data around to get this right, which is totally
uncool as this is actually a much larger spreadsheet (450 clients, six
categories to compare by year, four years; obviously my chart will only
highlight certain clients, but still...).

Use a separate sheet for your charting data. Copy the original data, use
Paste Special - Link (so the second sheet updates if the first sheet does)
to put it onto the second sheet, then drag the cells on the second sheet
into position. (Or use the original data as the source for a pivot table, if
it's properly arranged.) Then make your chart from the data on the second
sheet. This takes less time and effort than trying to force inefficiently
arranged data into a certain chart configuration.

I can't believe there's no "Secondary Series" option!

What? To plot a series on a secondary axis, double click on it, and on the
Axis tab, choose Secondary. You need at least two series to do this, at
least one must stay on the primary axis.

- Jon