Advanced Gantt Charts in Microsoft Excel.
Updated Tutorial on Peltier Tech Blog: Gantt Charts in Microsoft Excel
The information below was written for a very old version of Microsoft Excel. You may still find it useful, but a totally updated tutorial has been published on the Peltier Tech Blog.
Gantt charts are useful tools in program management, which help to show graphically when tasks must start and finish, and which tasks are underway at any given time. Gantt charts help in scheduling of the many tasks in a program, and in identifying potential resource issues in the schedule. A simple Gantt chart is merely a floating bar chart, that is, a stacked bar chart in which the first series is formatted to be invisible. The second series of bars are stacked on the first, but these bars appear to float in the middle of the chart, because the first series is formatted to be invisible. My article Gantt Charts in Microsoft Excel in Tech Trax e-zine describes this simple approach.
This example is more detailed, and therefore more complicated. There are two visible bars, so the floating bar can show fraction complete and fraction incomplete. In addition, two line chart series are added to show milestones for completed and not-yet-completed tasks. Excel will not allow an XY series to be added to a Bar-Line combination chart, so an additional line series is used as an anchor for a vertical line and label. Using a line chart allows us to use the versatile time scale axis of the line chart as the horizontal axis of the Gantt chart.
The table below contains the data for this example. The sections of the data range are described in more detail:
The user enters Task Name (column A), Start Date (B), either Duration (E) or End Date (F), and Percent Complete (K), as well as the data in the yellow range. The other columns are calculated.
These are the formulas that make the chart work.
Constructing the Chart
To create the chart, select the data in range F1:I11 (the blue and yellow shaded regions in the table above), start the Chart Wizard, and choose a Line chart in Step 1 of the wizard.
Delete the horizontal gridlines, and add vertical gridlines (Chart menu > Chart Options > Gridlines tab > Category Axis Major Gridlines).
Double click the horizontal (time) axis; on the Scale tab, set appropriate Minimum (4/1/01), Maximum (7/8/01), Major (14 days), and Minor (7 days) scale parameters. Also on the scale tab, uncheck the Value (Y) Axis Crosses Between Dates checkbox. On the Number tab, choose an appropriate date format (m/d).
Select and copy range A1:D10 (shaded green above), select the chart, and use Paste Special from the Edit menu to add the data as New Series.
Select the Start series, then use Chart Type on the Chart menu to change the series to a Stacked Bar type.
Select the Done series, and again use Chart Type on the Chart menu to change the series to a Stacked Bar type. Repeat for the Not Done series. You can use the F4 key to repeat the latest action for the Not Done series, but not for the Done series; for some reason, when you assign the Stacked Bar type to the Start series in the previous step, Excel considers it a Clustered Bar.
Choose Chart Options on the Chart menu, click on the Axes tab, and check the Secondary Category checkbox. This adds the list of tasks as axis labels.
Double click on the bottom time axis, and click on the Scale tab. Check the Value (Y) Axis Crosses at Maximum Value checkbox. This moves the numeric vertical axis from left to right (where the numbers temporarily overlap the task list on the right axis).
Double click on the top time axis, and click on the Scale tab. Uncheck the Category (X) Axis Crosses at Maximum Value checkbox. This moves the task list to the left side of the chart.
Double click left (Task) axis, and click on the Scale tab. Check the Categories in Reverse Order box and uncheck the Value (Y) Axis Crosses at Maximum Category box, so the tasks are represented from top to bottom. Enter 1 in the Number of Categories Between Tick Mark Labels box, to force Excel to display each label.
Double click on the right numerical axis. Check the Values in Reverse Order box and also the Category Axis Crosses at Maximum Value box. Make sure the minimum and maximum are set to 0 and 9, and uncheck the Auto box in front of each, so Excel doesn't unexpectedly change the axis. If more tasks are added, both vertical axes must be rescaled in tandem. On the Patterns tab, choose None for Tick Marks and Tick Labels to hide the numbers.
Double click the top axis. Set the scale parameters to: Minimum 4/1/01, Maximum 7/8/01, Major Unit 14, Minor Unit 7 to match the bottom time axis that was formatted earlier. Even though this is a value axis that is expecting a "regular" number, Excel will accept numbers in date format. On the Patterns tab, set Tick Mark Labels to None. Note: the top and bottom time scales must be synchronized (manually) when the data changes.
Format the bar timeline series and milestone markers. Make the Start series invisible by choosing None for Border and Area on the Patterns tab. Choose appropriate colors, marker shapes, and marker sizes for the other series.
Delete the Legend, and stretch the Plot Area to fill the whole chart.
Resize the plot area, moving down the top edge to make room for a label. Double click the Vert Line series (single point in this example), and add data labels, using the Category Name option. Double click the data label, and on the Alignment tab, choose Above for Label Position.
Double click on the Vert Line series, and on the Patterns tab, select None for Line and Marker. On the Y Error Bars tab, choose Plus (it goes down, but the axis is plotted in reverse order), with a Fixed Value of 9 (to stretch from top to bottom).
Peltier Technical Services, Inc., Copyright © 2018. All rights reserved.