Multiple data in series

[Ann Kelley]

I am trying to put multiple date in a series on an Excel chart (Excel
2000). I have two values that I want to show in one month. I want the
series to have seperate colors by value but totaled for the month. For
example, in June 2005, there is $200 in sales of cookies and $200 in
sales of candy. I want to show $400 in sales, but show the series as
$200 (green) indicating cookies and $200 (yellow) indicating candy.

 

[Pim]

Hi Anne-
I think this is what your after: a stacked column graph.
put the data in table format:
Month $Cookies $Candy

Select the whole table and make a chart (either click the chart icon,
or select chart from the insert menu) This will start the chart wizard,
choose column as chart type and stacked column (top row, second from the
left) as the sub type. The wizard will walk you through the rest.
Good luck,
pim

 

[Ann Kelley]

What I have is data for January through Decemer for the years 2002
2005. Now I need to start seperating data for each month in tw
catagories. I don't even know if it is possible to have a stacke
column chart. I need a chart that shows at the bottom each mont
grouped together with a series for each year. Then starting in June o
2005, showing two values in one series stacked. Like all this tim
until June 2005 we were counting cookies and candy as sales togethe
but now we want to show in the single series stacked the total o
cookies and candy. :eek

 

[Jon Peltier]

Did you try Pim's suggestion?

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

 

[Pim]

Hi Ann
hm...
well, try this as an example and see if it looks right-
if not, indicate what's off and we'll go from there:
(that way you don't have to go through the pain of formatting your
data-
then finding out this isn't quite right)

type in into an new sheet:
cell a1:Month
B1: Total Cookies
C1:Total Candy

A2: June
B2:500
C2: 750

A3: July
B3: 250
C3: 350

A4: August
B4: 450
C4: 500


highlight the cells in A1:C4 and start the chart wizard-
and take it from the first post.
If you're looking for an easy way to separate the data, that's a whole
new ball game

 

[Ann Kelley]

I did try Pim's suggestion. Pim, your last entry I tried and it worke
although I have too much data for the graph to look right. On the lef
column, the data is months. At the top I have years 2002-2005. In Jun
2005, my supervisor decided that we need to split out the data and h
wants to show the data in the same column for that month but show it i
different "colors" indicating the split data. He still wants the grap
to show the years 2002-2005. I guess I am coming to think that ther
is no way to have a stacked graph that will show the data the way i
will look presentable. Thanks for your help! :

 

[Jon Peltier]

What is good about Pim's suggestion and bad about its use with your data? Why does
"too much data" make the chart not look right? How do you need to change its
formatting to make it presentable?

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

 

[Ann Kelley]

What was good about Pim's suggestion was I was able to show the data fo
June 2005 stacked. My original chart has the labels at the bottom fo
just the month and they are grouped. So the first group is January an
there are 4 bars showing each year under that label. The next label i
February and so on. When I changed the graph to Pim's suggestion, th
months are no longer grouped. Each column shows the data label a
"Jan-02", "Jan-03", etc. I want the chart to look like my origina
chart showing the groups with individual columns for the year and t
start stacking the figures for each month (2 different values).

Thanks,
Ann :rolleyes

 

[Jon Peltier]

I'm having a little trouble visualizing this (sorry, it's getting late). Perhaps it
would help if you included a sample of your data, so we can see how it's arranged,
then explain to us what's clustered and what's stacked. (Make sure you type slowly,
so we can keep up!)

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

 

[Ann Kelley]

I just added my excel spreadsheet.

 

[Ann Kelley]

Okay, I thought I uploaded the spreadsheet! Not sure where it went.

 

[Jon Peltier]

I just meant pasting some of the data into a reply. Some interfaces
strip out the attachments, and a lot of people don't open them anyway.

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