Combo Gant and Line Chart

[ExcelMonkey]

I want to build a combo Gant and Line Chart. I want each to have their own X
and Y axes (i.e. 2 X and 2 Y axes). My Gant Chart is set up as follows:

Days
Phase 1 30
Phase 2 60
Phase 3 90
Phase 4 90

My data is in A1:B5. with the "Days" label in B1. It is set up as stacked
bar chart with the series overlap separated set at 0% to create the layout of
a Gant Chart. It runs left to right plotted on the bottom X Axis.

I want to find a way to add another data series to this chart which shows
the cashflow over 30 days periods. I want this to be a line chart with the
cash on the second Y axis and the 30 day increments on the second X axis. I
think I have to set the data up differntly to do this. Effectively Stages
1-3 are money losers and Stage 4 generaes postive cashflow (hopefully the
example comes through ok in the forum).

30 60 90 120 150 180 210
240 270
Cashflow -10 -10 -10 -10 -10 -10 20
20 20
Phase 1 X
Phase 2 X X
Phase 3 X X X
Phase 4
X X X


I am not sure how to set up the data and illustrate the graph as a combo
Gant Chart and line series combo?

Any ideas?

Thanks

EM

 

[Jon Peltier]

If you make it a combination Gantt/XY, you can use this tutorial as a
starter. Skip the XY series for milestone markers and use it instead for
cash flow.. Separate the two charts into their own panels (Gantt above, cash
flow below).

http://peltiertech.com/Excel/Charts/GanttChart.html

- Jon

 

[ExcelMonkey]

I didn't relalize how much the chart interace has changed in Excel 2007. I
am trying to get up to speed to try to replicate your example which was in
Excel 2003.

You say:
"Choose Chart Options on the Chart menu, click on the Axes tab, and check
the Secondary Category checkbox. This adds the list of tasks as axis labels."

This is in the section where you are trying to put the Tasks on a secondary
verticle axes.

How do you do this in Exel 2007? I know you need to pull up the "Series
Option" dilague (this has replaced the "axes tab" which was under "Chart
Options" in Excel 2003). However I cannot get this dialogue to pop up unless
I click onto a series on the chart. For example if you:
1) select a series
2) Goto the Format tab
3) Click onto the Fomrat Selection in the Current Section group
4) The Series Option comes up but its greyed out

Is there another way to pull this dialogue up?

Thanks

EM

 

[Jon Peltier]

All the dialogs have changed, and some haven't been replaced.

You can right click on a series and choose Format, and find the
Primary/Secondary selection on the first or second "tab" (they don't really
look like tabs anymore). To do the Axes, you need to use the middle of the
Chart Tools contextual tabs, I think it's "Layout", and select the axis you
want from the Axis or Axes dropdown, then format the axis to choose category
(text) or time (date) settings.

- Jon

 

[ExcelMonkey]

Thanks that was helpful!

EM

All the dialogs have changed, and some haven't been replaced.

You can right click on a series and choose Format, and find the
Primary/Secondary selection on the first or second "tab" (they don't really
look like tabs anymore). To do the Axes, you need to use the middle of the
Chart Tools contextual tabs, I think it's "Layout", and select the axis you
want from the Axis or Axes dropdown, then format the axis to choose category
(text) or time (date) settings.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______