Excel Help for Project Gantt Charts

Project 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:

	A	B	C	D	E
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).

Project Gantt Charts

Excel Help for Project Gantt Charts in Planning and Managing Projects

Project Gantt Charts

Excel templates and solutions matched for Project Gantt Charts:

	Gantt Chart Excel Template with built-in Smarts Rate this: (4/5 from 2 votes) 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 Megan Wale on 19 Jun 2018

	Good One 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 Wholesale-Dealer on 09 Aug 2010

	You are genius!! 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 Kynthia Melissa on 18 Jan 2011

	Project Gantt Charts 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 skledeny on 11 Jul 2011

	Thanks Megan Rate this: (2/5 from 2 votes) Hi, Thanks for the reference. It really helps.
	Techno-PM
	Posted by Technopm on 19 Jun 2018