Home > Forum Home > Planning and Managing Projects > Project Gantt Charts Share

Project Gantt Charts

Excel Help for Project Gantt Charts in Planning and Managing Projects


Forum TopicPost Reply Login

Project Gantt Charts

Rate this:
(4/5 from 2 votes)
SurprisedProject gantt charts provide a powerful tool for visualizing the project task schedules and status. Gantt charts can be created in Excel by utilizing that start and end dates of task lists and coloring cells accordingly.

The following example shows how this can be done with basic functionality. Ideas are offered at the bottom of this post on how to add further functionality to the project Gantt chart creation by modifying the basic code.

Suppose we have a simple task list in a worksheet as follows.

Excel Spreadsheet:
 ABCDE
1    Day 
2 Start Date End Date  Gantt Chart >> 
3 2/01/2006 3/01/2006   
4 3/01/2006 10/01/2006   
5 5/01/2006 12/01/2006   


Note that the dates are in d/mm/yyyy format. We would then like to have a Gantt chart created with date heading from cell D2 out and the cells colored accordingly for each task below. This can be accomplished with following VBA routine.

VBA Code:
Sub Gantt_Chart()
Application.ScreenUpdating = False
Dim mindate As Date
Dim maxdate As Date
Dim startcell As String
Dim columnoffset As Integer
Dim frequency As Integer
Dim task As Variant
startcell = "A3" 'Change this as necessary
columnoffset = 3 'Where to start the gantt chart
frequency = 1 'Could be 7 for weekly chart
'Get minimum and maximum dates
Range(startcell).Select
Range(Selection.End(xlToRight), Selection.End(xlDown)).Select
mindate = Application.WorksheetFunction.Min(Selection)
maxdate = Application.WorksheetFunction.Max(Selection)
'Create date headings
Range(startcell).Offset(-1, columnoffset).Select
ActiveCell.Formula = mindate
ActiveCell.Offset(0, 1).Select
Do Until ActiveCell.Offset(0, -1).Value >= maxdate
    ActiveCell.Formula = ActiveCell.Offset(0, -1).Value + frequency
    ActiveCell.Offset(0, 1).Select
Loop
'Create gantt chart
Range(startcell, Range(startcell).End(xlDown)).Select
For Each task In Selection
    mindate = task.Value
    maxdate = task.Offset(0, 1).Value
    task.Offset(0, columnoffset).Select
    'Get starting cell
    Do Until Cells(Range(startcell).Row - 1, ActiveCell.Column).Value >= mindate
        ActiveCell.Offset(0, 1).Select
    Loop
    'Color cell until end date
    Do Until Cells(Range(startcell).Row - 1, ActiveCell.Column).Value > maxdate Or Cells(Range(startcell).Row - 1, ActiveCell.Column).Text = ""
        ActiveCell.Interior.ColorIndex = 10
        ActiveCell.Offset(0, 1).Select
    Loop
Next
Range(startcell).Select
Application.ScreenUpdating = True
End Sub

This code can be simply copied into the VBA editor and run against the example spreadsheet to view the result. 

A number of extra features could be added here:
  • Reduce the width of the columns from D onwards and set the direction of the date heading text to read from bottom to top in order to view a longer time period without scrolling.
  • Add borders and/or patterns to the cells when coloring them.
  • Include a column or more to specify project phases, participants and/or whether the task is complete. Check this value for each task and color the tasks cells differently to show this detail.
  • Remove weekends from the chart by using the WEEKDAY function and check for the values 1 (Sunday) and 7 (Saturday).
  • Include values within the cells or comments on the cells with information about the task (e.g. hours, budget, participant information).
 Excel Business Forums Administrator
 Posted by on
 
Replies - Displaying 1 to 5 of 5Order Replies By: Most Recent | Chronological | Highest Rated
Confused
Rate this:
(3/5 from 1 vote)
hm this code is can be very useful it will be used at the right place
 http://www.dailytrader.com
 Posted by on
Applaud
Rate this:
(3/5 from 1 vote)
Hello there,

You have posted a really helpful code. I wrote the code and executed it, but found few warnings. Perhaps there might be few mistake. Then with the help of our professionals I was able to sort it out. Well I thank you for your work. Hope to see you around.

Thanks,
- Kynthia Melissa.
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Good post.I have executed the code and it worked well with few warnings.Thanks for giving us the code. [http://www.prominere.com/] | [http://www.prominere.com/]
 Posted by on
Confused A Gantt Chart could be a simple tasks vs time representation or a complex representation which shows tasks, resources, dependencies and critical path of the project. 

The above code is excellent but why not give a try to already ready to use excel gantt chart template.

URL  Youtube shows video on how to use Gantt Chart Excel Template with built-in Smarts and download templates  Here

Hope this helps you.

Thanks!!
 Megan Wale
 Posted by on
Confused
Rate this:
(2/5 from 2 votes)
Hi,

Thanks for the reference. It really helps.
 Techno-PM
 Posted by on
 Displaying page 1 of 1 

Excel templates and solutions matched for Project Gantt Charts:

Solutions: Project Planning and Management Gantt Chart Creator Custom Gantt Charts Project Management Templates