Excel for Project Management: Gantt Charts, Trackers, and Timelines

Project Task List Structure

Essential columns for task tracking:

• Task ID and name
• Start date and end date
• Duration (calculated or manual)
• Owner/assignee
• Status (Not Started, In Progress, Complete, Blocked)
• % Complete
• Dependencies (which tasks must finish first)

Building a Gantt Chart

Create a Gantt chart using conditional formatting:

1. Create date headers across the top (one column per day/week)
2. Use conditional formatting formula: =AND(H$1>=$C2, H$1<=$D2)
3. Apply a fill color when the formula is true
4. Result: bars appear for each task's duration

Add a second condition with different color for completed portions.

Duration and Date Calculations

Working days duration: =NETWORKDAYS(StartDate, EndDate)

End date from duration: =WORKDAY(StartDate, Duration)

Days remaining: =MAX(0, EndDate-TODAY())

Days overdue: =IF(AND(Status<>“Complete”, EndDate<TODAY()), TODAY()-EndDate, 0)

Milestone Tracking

Flag milestones separately from regular tasks:

• Add a “Type” column (Task vs. Milestone)
• Milestones have duration = 0
• Use different Gantt formatting (diamond shape or different color)
• Create a milestone summary view with FILTER or QUERY

Resource Allocation View

Track who's working on what:

=SUMPRODUCT((Owner=“John”)*(StartDate<=WeekEnd)*(EndDate>=WeekStart))

This counts tasks assigned to John that overlap with the given week. Create a matrix with team members as rows and weeks as columns.

Project Dashboard Metrics

• % Complete: =AVERAGE(PercentComplete) or SUMPRODUCT for weighted
• Tasks on track: =COUNTIFS(Status,“<>Complete”,EndDate,“>=”&TODAY())
• Tasks at risk: =COUNTIFS(Status,“<>Complete”,EndDate,“<”&TODAY())
• Days to deadline: =ProjectEndDate-TODAY()

Official Resources

• WORKDAY function — calculate working days
• NETWORKDAYS function — count working days