For complex dashboard: What type of chart to use?

[Bev]

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
(e-mail address removed)

 

[Jon Peltier]

Check out these articles about Excel Gantt Charts:

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

- Jon

 

[Bev]

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]

Task and milestone names: Use data labels. Doing this manually is a chore,
but there are a couple free Excel add-ins you can download to make this much
easier:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com

You can make the formulas as complicated as you want. To get different
colored bars, create multiple series, so that at each category you have a
dummy invisible bar (on which other bars float), then one or more other
bars, depending on which color(s) you want. If a color is not to appear at a
given category, the associated value should be zero.

- Jon

 

[Bev]

Thanks again, Jon. I'll give one of the addins a try. Bev