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.
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
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 two catagories. I don't even know if it is possible to have a stacked column chart. I need a chart that shows at the bottom each month grouped together with a series for each year. Then starting in June of 2005, showing two values in one series stacked. Like all this time until June 2005 we were counting cookies and candy as sales together but now we want to show in the single series stacked the total of cookies and candy.
Did you try Pim's suggestion?
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Ann Kelley wrote:
> 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 two
> catagories. I don't even know if it is possible to have a stacked
> column chart. I need a chart that shows at the bottom each month
> grouped together with a series for each year. Then starting in June of
> 2005, showing two values in one series stacked. Like all this time
> until June 2005 we were counting cookies and candy as sales together
> but now we want to show in the single series stacked the total of
> cookies and candy.
>
>
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
I did try Pim's suggestion. Pim, your last entry I tried and it worked although I have too much data for the graph to look right. On the left column, the data is months. At the top I have years 2002-2005. In June 2005, my supervisor decided that we need to split out the data and he wants to show the data in the same column for that month but show it in different "colors" indicating the split data. He still wants the graph to show the years 2002-2005. I guess I am coming to think that there is no way to have a stacked graph that will show the data the way it will look presentable. Thanks for your help!
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 wrote:
> I did try Pim's suggestion. Pim, your last entry I tried and it worked
> although I have too much data for the graph to look right. On the left
> column, the data is months. At the top I have years 2002-2005. In June
> 2005, my supervisor decided that we need to split out the data and he
> wants to show the data in the same column for that month but show it in
> different "colors" indicating the split data. He still wants the graph
> to show the years 2002-2005. I guess I am coming to think that there
> is no way to have a stacked graph that will show the data the way it
> will look presentable. Thanks for your help!
>
>
What was good about Pim's suggestion was I was able to show the data for June 2005 stacked. My original chart has the labels at the bottom for just the month and they are grouped. So the first group is January and there are 4 bars showing each year under that label. The next label is February and so on. When I changed the graph to Pim's suggestion, the months are no longer grouped. Each column shows the data label as "Jan-02", "Jan-03", etc. I want the chart to look like my original chart showing the groups with individual columns for the year and to start stacking the figures for each month (2 different values).
Thanks,
Ann
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 wrote:
> What was good about Pim's suggestion was I was able to show the data for
> June 2005 stacked. My original chart has the labels at the bottom for
> just the month and they are grouped. So the first group is January and
> there are 4 bars showing each year under that label. The next label is
> February and so on. When I changed the graph to Pim's suggestion, the
> months are no longer grouped. Each column shows the data label as
> "Jan-02", "Jan-03", etc. I want the chart to look like my original
> chart showing the groups with individual columns for the year and to
> start stacking the figures for each month (2 different values).
>
> Thanks,
> Ann
>
>
I just added my excel spreadsheet.
Okay, I thought I uploaded the spreadsheet! Not sure where it went.
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/
_______
Ann Kelley wrote:
> Okay, I thought I uploaded the spreadsheet! Not sure where it went.
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks