stacked column chart with line or points

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello! I am trying to create a stacked column chart with 2 sets of data in each stack and add a line (or points) for a third set of data. My data is set up as indicated below and will have all 24 months of data when completed. There should be a stack for the Jan 03 TOTAL DEV/TOTAL PROD data, etc with the Jan 03 trade volume as a point or a line on a secondary axis. I've done similar charts before but can't seem to get this type to work. Any suggestions??

TRADE VOLUME TOTAL DEV TOTAL PRO

January 03 23,000,000 $125,000 $700,00
January 04 18,000,000 $158,000 $550,00

February 03 18,200,000 $140,000 $580,00
February 04 13,600,000 $198,000 $525,00

March 03 0 $0 $
March 04 0 $0 $0
 
Stephanie -

Make your chart with all the data as a stacked column chart. Select the
trade volume series, choose Chart Type from the Chart menu, and select a
Line style. Double click the new line series, and on the Axis tab,
select Secondary.

- Jon
 
Jon, thanks for the reply. I tried this and was able to get the trade volume series on the secondary Y axis. However, the lines are connecting Jan 03 to Jan 04 and Feb 03 to Feb 04 instead of Jan 03 to Feb 03, etc. Is there a way to fix this? If not, I'd be happy with just a point (marker) on the stacked column where the trade volume would be. Also my X axis labels are incorrect. Instead of showing Jan 03, Jan 04, Feb 03, etc. I only show Jan 03 Mar 03 May 03, etc. Any idea how I can fix this?

----- Jon Peltier wrote: ----

Stephanie

Make your chart with all the data as a stacked column chart. Select the
trade volume series, choose Chart Type from the Chart menu, and select a
Line style. Double click the new line series, and on the Axis tab,
select Secondary

- Jo
------
Jon Peltier, Microsoft Excel MV
Peltier Technical Service
http://PeltierTech.com/Excel/Charts
______

stephanie wrote
 
Stephanie -

Oh, so the lines zigzag forward and backward? I hadn't noticed your data
wasn't sorted. If you sort the data it will connect the points in order.
Actually, if you have a time scale axis, the lines should connect the
points in chronological order anyway, unless Microsoft "fixed" something
after Excel 2000.

- Jon
 
Stephanie -

Try one of these steps first:

Sort your data in ascending order by date.

or

Right click on the zigzag series, select chart type from the pop up
menu, and choose a Line chart type for this series.

If neither of these works, then I'll look at your file. You should be
able to clean up the address.

- Jon
 
Jon, I tried your suggestion but then the months are not clustered as I need. I've sent you an email with an attachment showing you the data and subsequent graphs. I'd really appreciate it if you could take a look at my data and the resulting graphs. Thank you!!:


----- Jon Peltier wrote: ----

Stephanie

Try one of these steps first

Sort your data in ascending order by date

o

Right click on the zigzag series, select chart type from the pop up
menu, and choose a Line chart type for this series

If neither of these works, then I'll look at your file. You should be
able to clean up the address

- Jo
------
Jon Peltier, Microsoft Excel MV
Peltier Technical Service
http://PeltierTech.com/Excel/Charts
______

stephanie wrote
 
Jon, thank you so much for your sample! It was EXACTLY what I was looking for!!!! I can't begin to tell you how many people I've asked and how much time I've spent on this exercise. I was so close yet....not quite there. Thanks again for staying with me on this. You are GREAT!!

I've placed an example of the data layout below for others who may be interested: #NA is placed in each of the blank cells on the spreadsheet. Dev Total & Prod Total are stacked columns on the X axis and the volume colums are separate lines connecting the monthly data points for each year.

Volume 03 Volume 04 Dev Total Prod Total
#NA #NA
Jan 03 10,000,000 #NA $120,000 $700,000
Jan 04 #NA 12,000,000 $150,000 $600,000
#NA #NA
Feb 03 13,000,000 #NA $140,000 $570,000
Feb 04 #NA 14,000,000 $200,000 $525,000


----- stephanie wrote: -----

Jon, I tried your suggestion but then the months are not clustered as I need. I've sent you an email with an attachment showing you the data and subsequent graphs. I'd really appreciate it if you could take a look at my data and the resulting graphs. Thank you!!:)


----- Jon Peltier wrote: -----

Stephanie -

Try one of these steps first:

Sort your data in ascending order by date.

or

Right click on the zigzag series, select chart type from the pop up
menu, and choose a Line chart type for this series.

If neither of these works, then I'll look at your file. You should be
able to clean up the address.

- Jon
 
Back
Top