For complex dashboard: What type of chart to use?

This is for a quarterly project report. Some execs are not using Project,
but Excel, so need to build a template for them to create their summaries.
They have a vision of a 3-months timescale at top, on the horizontal scale,
with high level deliverables along the vertical scale. The chart would have
details regarding work on the project (expressed as tasks).

X Axis: Deliverables 1, 2, 3, n
Y Axis:
A. Timescale, in months. (At top of chart)
B. Milestones Completed, Tasks (Floating in chart, in line with their
corresponding deliverable)

Started but not Finished, and Unstarted Tasks show along the Y Axis as
different colored bubbles or boxes surrounding the milestones and tasks.

There are three types of tasks that have to be represented – completed,
underway, and next steps. Each should have a different color.

I’ve had trouble getting the charts used so far to order the tasks in the
original, chronological order. Excel wants to take them and reorder them
based on the numeric data. I could use some help with that. Also, I think I
can do the chart as described by customizing a scatter diagram – can someone
confirm this or suggest something else?

Thanks!

Bev Powell
[email protected]

Check out these articles about Excel Gantt Charts:

http://pubs.logicalexpressions.com/P...cle.asp?ID=343
http://peltiertech.com/Excel/Charts/GanttChart.html
http://peltiertech.com/Excel/Charts/GanttLinks.html

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


"Bev" <[email protected]> wrote in message
news:[email protected]...
> This is for a quarterly project report. Some execs are not using Project,
> but Excel, so need to build a template for them to create their summaries.
> They have a vision of a 3-months timescale at top, on the horizontal
> scale,
> with high level deliverables along the vertical scale. The chart would
> have
> details regarding work on the project (expressed as tasks).
>
> X Axis: Deliverables 1, 2, 3, n
> Y Axis:
> A. Timescale, in months. (At top of chart)
> B. Milestones Completed, Tasks (Floating in chart, in line with their
> corresponding deliverable)
>
> Started but not Finished, and Unstarted Tasks show along the Y Axis as
> different colored bubbles or boxes surrounding the milestones and tasks.
>
> There are three types of tasks that have to be represented - completed,
> underway, and next steps. Each should have a different color.
>
> I've had trouble getting the charts used so far to order the tasks in the
> original, chronological order. Excel wants to take them and reorder them
> based on the numeric data. I could use some help with that. Also, I
> think I
> can do the chart as described by customizing a scatter diagram - can
> someone
> confirm this or suggest something else?
>
> Thanks!
>
> Bev Powell
> [email protected]
>
>
>
>

Jon:

Thanks a ton for the reply! Your articles are VERY helpful. I’m following
your instrux in the Advanced Gantt article to get a basic picture.

Now here are some of the wrinkles:

We want the task names and milestone names to appear in the chart itself,
not as labels on either axis. They should fill the bar, box or bubble that
represents the timeframe during which they are being completed.
“Deliverables” will be on the left, as the major scale on the horizontal
axis. I think this can be done if I use the Excel template for Project that
has fields for tasktable, resourcetable, and assignmentstable. On the
tasktable tab, in my task name column (B), Deliverables are always associated
with Outline Level 3 in column G. So the formula should be =IF (G5=3, B5) to
create the left horizontal axis.

I plan to do the series data differently too, not based on % complete but on
more IF statements, comparing Start and Finish. Does this make sense to you?

Also, do you have suggestions for formatting the chart title? To get the
major scale, I did a line chart for the three month period, but I can’t seem
to separate the three months to encapsulate each in a separate color field,
which is what management prefers.

Thanks again,

Bev




"Jon Peltier" wrote:

> Check out these articles about Excel Gantt Charts:
>
> http://pubs.logicalexpressions.com/P...cle.asp?ID=343
> http://peltiertech.com/Excel/Charts/GanttChart.html
> http://peltiertech.com/Excel/Charts/GanttLinks.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "Bev" <[email protected]> wrote in message
> news:[email protected]...
> > This is for a quarterly project report. Some execs are not using Project,
> > but Excel, so need to build a template for them to create their summaries.
> > They have a vision of a 3-months timescale at top, on the horizontal
> > scale,
> > with high level deliverables along the vertical scale. The chart would
> > have
> > details regarding work on the project (expressed as tasks).
> >
> > X Axis: Deliverables 1, 2, 3, n
> > Y Axis:
> > A. Timescale, in months. (At top of chart)
> > B. Milestones Completed, Tasks (Floating in chart, in line with their
> > corresponding deliverable)
> >
> > Started but not Finished, and Unstarted Tasks show along the Y Axis as
> > different colored bubbles or boxes surrounding the milestones and tasks.
> >
> > There are three types of tasks that have to be represented - completed,
> > underway, and next steps. Each should have a different color.
> >
> > I've had trouble getting the charts used so far to order the tasks in the
> > original, chronological order. Excel wants to take them and reorder them
> > based on the numeric data. I could use some help with that. Also, I
> > think I
> > can do the chart as described by customizing a scatter diagram - can
> > someone
> > confirm this or suggest something else?
> >
> > Thanks!
> >
> > Bev Powell
> > [email protected]
> >
> >
> >
> >
>
>
>