Gantt charts in Excel

Introduction

Gantt charts are used to graphically display the use of resources (machines, tanks, pipes, operators, products) over time. This help information is understood best in combination with the Excel document gantt.xls (for Netscape browsers: Shift click to download). This Excel document reads its input data from the example file gantt.txt.

Download the 2 documents into a directory on your local disk. First start up Excel, then open the file/workbook gantt.xls. The workbook contains 5 different worksheets: (1) the actual Gantt chart sheet named: "Gantt chart", (2) the parameter set up sheet "GanttLayout" that must be edited by the user, (3) the sheet "SimData" into which the simulation results are read in from the file "gantt.txt", (4) and (5) the sheets "ChartData" and "TimeData" that are used by Excel to store intermediate results.

Sheet "GanttLayout"

The data in this sheet should match that data in the input file "gantt.txt". This input file may be generated by simulation of a Chi model.

• Column D: "Machines" should define the names of all resources used in the data input file "gantt.txt". In the example 13 different resources are defined: tanks T0 to T7; pipes TS, TS0, TS1 (TS = tankstraat), VL (vulleiding), and LL (leegleiding). Not all names defined in column D need to be used in the data file, but all names that are used in the data file must be present in column D.
• Column E: "Sequence nr." defines the sequence in which the resources/machines are listed on the left axis in the "Gantt chart" sheet.
• Column A: "Product or action and colour" defines the different possible actions that are performed by the resources/machines. In the example: "VR" (vullen reinigen), "Fer" (fermenteren) and "LR" (legen reinigen). The colour of the cell defines the colour of the action in the gantt chart.
• Column B: contains the colour codes of the colours in column A. This column is generated automatically by Excel, and should not be edited.
• Cell H3 contains the major time division (24 in the example) of the generated gantt chart, and cell H5 contains the minor division (6 in the example).
• Cell H9 contains the name of the data input file ("gantt.txt" in the example).
• Cell H13 contains the number format (can be "0", "0.0", "0.00" etc.). "0.0" means that the duration times of actions in the Gantt chart are rounded to one decimal after the comma. "0" means that the intervals are truncated to no decimals. The duration times are shown by hovering the cursor over the blocks in the Gantt chart.

Use of the Gantt chart

Activate the Gantt chart by clicking on sheet "Gantt chart". This brings up a menu with items: "Gantt Chart Multiple Steps", "Gantt Chart Single Step" and "Help" (currently, no help is defined, only "About" information). You may start with the Single Step Menu. Item "Gantt Chart Without Text (fast)" first reads in the data file "gantt.txt" into the sheet "SimData", then it draws the Gantt chart. This may take several seconds. The bars in the Gantt chart are drawn without the text of the actions. If you want to include the text use menu item "Gantt Chart With Text", but this can be very slow to execute!

On some systems Excel may crash. If this happens, use the menu items on "Gantt Chart Multiple Steps". First execute item "Import Data", then "Prepare Gantt Data", and then either "Gantt Chart Without Text (fast)" or "Gantt Chart With Text". This should make Excel less likely to crash.

Format of the input data file

Every line of input file "gantt.txt" should consist of 1 action that is executed on/by 1 resource. The format of an action is as follows:

MachineName TAB starttime TAB endtime TAB actionName

For example:

T1 TAB 10 TAB 45 TAB VR

This line indicates that resource "T1" was busy with action "VR" from time point 10 upto time point 45. TAB indicates a tab character.

Last updated: 2 November 1998