A Gantt chart is a type of bar chart to show the project scheduling. It is the most widely used project scheduling method for easy understanding of the project. In these activities shown on the vertical axis of the chart and time duration shown in the horizontal axis of the chart. The width of the bar shows the time duration it is going t0 take for completion. It was first developed by Mr. Henry Gantt so as its name called as Gantt chart. It is the basic and simplest tools for representing the project schedule to the higher management. It is mainly of two types of
- Progress Gantt chart in which the progress is shown in different color or hatching on the same bar for project progress and remaining work till date
- Linked Gantt chart in which the relation and dependency between the activities shown with the arrow.
At the initial time Gantt chart was used to made through hand only by sketching but after the invention of the computer and it is widely used in every industry, Gantt charts are also being made using computer programs. Gantt chart view is the default UI view of the most project management software. The problem with these software is that you have to buy the license for these software to use in your work. MS Excel is the most widely used office software for data calculation and representing, as so easily available on every computer. We can easily make simple Gantt chart in excel by filling colors in the cells which we want to represent but the same will not be dynamic and will not be changed with the starting, end date, duration and progress of that activity.
There are six methods of doing this in excel for different inputs. We are also enclosing the excel file for all these six methods. click on the link http://projectplanner.in/downloads
- Gantt chart for activities shown in a month with progress bar
In this excel sheet, starting date and duration input. % Progress update (25%,50%,75%,100%) to be choose from drop-down menu.
Put following two formulas in conditional formatting tab
- For work progress achieved
- For work remaining
- Select entire graphical area ( $H$7:$AL$23) for applying conditional formatting
- Gantt chart for activities shown in a year
In this excel sheet starting and end date will be the input, based on the month of start and end date the cells will be highlighted.
Put following formula in conditional formatting formula tab
- For highlighting the cell between the start and end date months
- Gantt chart for activities shown in a year with the progress update
In this excel sheet though conditional formatting used but the formula not used in the formula bar, instead of a simple cell contains conditions used. start date and end date are the inputs in sheet and progress status (Completed and Pending) to be chosen from drop-down menu.
- use following formula in all the excel chart area
It will show either completed, pending or blank in each cell of the Gantt chart area.
- Use conditional formatting tool to highlight cell which contains “completed” written with green color and “pending” with red color
- To hide the text written in excel cells go to – Formatting cell- custom formatting – and write (;;;) for number formatting. It will hide the text from the cells.
- Gantt chart for yearly tasks showing critical path
In this sheet conditional formatting along with the formulas used for showing the critical path and dates of all the activities. Task ID, Start date for 1st task, Predecessor 1 ID, Predecessor 2 ID and Duration are only inputs and based on these start dates, end dates, duration and predecessor IDs critical path will be highlighted
- Formulas used for finding the earliest start, latest start, latest start and latest end duration.
- Based on these formulas and dependencies, dates are calculated for each task and then conditional formatting being used
- Gantt chart based on Excel VBA
In this sheet excel VBA macro code used for showing the Gantt chart. Start and end date of the activity is only input to the sheet and macro button enabled on the sheet. After filling the date macro button Gantt chart can be pressed to make Gantt chart immediately for months of the start and end date
- Following is the VBA code used in the Macro
‘gantt chart based on Start and End date
Range(“E8:P36”).Borders.LineStyle = xlContinuous
Dim X As Integer
Dim Y As Integer
Dim Z As Integer
X = 0
Y = 0
Do Until ActiveCell = “”
Y = (Month(ActiveCell) – Month(ActiveCell.Offset(0, -1)))
Z = Month(ActiveCell.Offset(0, -1))
For Y = 0 To Y
ActiveCell.Offset(0, 1 + Z + Y).Interior.ColorIndex = 33
X = X + 1
- Gantt chart based on excel chart property
In this, there are some nos. of easy steps for creating the Gantt chart in the excel sheet. Task name or no., starting date and duration are the only inputs. So, following are the steps for this
- Insert chart with chart style bar chart – stacked chart
- Now right click on the chart and take the select data tab
- Use two series in the Legend entry (series) 1. Start date 2. Duration. Select data corresponding to these series.
- In the Horizontal (Category) Axis label use task name or nos. as shown in the excel sheet
- Now it will show the chart with two distinguished color bars horizontally.
- Select the first bars and right click to the formatting bars, go to fill and choose no fill option.
- Now bars are showing in the wrong order, to change the order select the vertical bars showing task ID and Task name right click for the format axis, choose categories in reverse orders.
- Now the bars are shown in the right order. To fix the starting and ending duration again select the date axis and go to format axis an then axis option. Here dates are taken as nos and we can choose to fix nos. in minimum and maximum tabs. We can put the nos. based on the duration for which we required to show the gantt chart.