My Excel Gantt Chart

5/27/2018 My Excel Gantt Chart

1/20

A B C D E F G H

1 Start Done Not Done Duration End Finished

Unfinishe

d

2 Task 1 4/5/2001 14 0 14 4/19/2001 0.5 #N/A

3 Task 2 4/12/2001 21 0 21 5/3/2001 1.5 #N/A

The milestone values in column J (0.5, 1.5, etc.), which columns G and H link to, are de

and Unfinished markers at the mid-height of the Done/Not Done bars.

The user enters Task Name (column A), Start Date (B), either Duration (E) or End Date (as well as the data in the yellow range. The other columns are calculated.

e ue s a e range co umns t roug conta ns t e ne c art ata use to p ot m

time scale horizontal axis. Column I within the blue range is filled with #N/A errors, ent

#N/A in the cells. In a Line or XY chart series, no marker is plotted for #N/A; if there ar

the connecting line connects these valid points, interpolating across the #N/A.

e ye ow s a e range e ow e ue s a e range s an ex ens on o e ne c ar

the vertical line(s); add more rows to add more vertical lines. The value in the yellow cel

dynamically linked to the current date by inserting the formula =TODAY()in the cell. Colu

yellow range is filled with #N/A errors, enter the formula =NA()or type #N/A in the cells

cell(s) of column I is selected to put the point at the top edge of the chart.

Sample Data

The table below contains the data for this example. The sections of the data range are de

The green shaded range (columns A through D) contains the stacked bar data which is u

the chart.

Gantt charts are useful tools in program management, which help to show graphically whfinish, and which tasks are underway at any given time. Gantt charts help in scheduling o

program, and in identifying potential resource issues in the schedule. A simple Gantt char

chart, that is, a stacked bar chart in which the first series is formatted to be invisible. The

stacked on the first, but these bars appear to float in the middle of the chart, because the

invisible. My article Gantt Charts in Microsoft Excel in Tech Trax e-zine describes this simp

s examp e s more eta e , an t ere ore more comp cate . ere are two v s e ar

show fraction complete and fraction incomplete. In addition, two line chart series are add

completed and not-yet-completed tasks. Excel will not allow an XY series to be added to a

so an additional line series is used as an anchor for a vertical line and label. Using a line c

versatile time scale axis of the line chart as the horizontal axis of the Gantt chart.

Advanced Gantt Charts in Microsoft Excel.

Introduction


2/20

4 Task 3 4/25/2001 10.5 3.5 14 5/9/2001 #N/A 2.5

5 Task 4 4/25/2001 21 7 28 5/23/2001 #N/A 3.5

6 Task 5 5/15/2001 7 7 14 5/29/2001 #N/A 4.5

7 Task 6 5/18/2001 7 21 28 6/15/2001 #N/A 5.5

8 Task 7 5/18/2001 12.25 22.75 35 6/22/2001 #N/A 6.5

9 Task 8 5/25/2001 8.75 26.25 35 6/29/2001 #N/A 7.5

10 Task 9 6/5/2001 0 24 24 6/29/2001 #N/A 8.511 6/1/2001 #N/A #N/A

Cell FormulaC2: 0

D2: 0E2*: 0

F2 : 0

G2: #N/A

H2: 0

J2: 49.5

Delete the horizontal gridlines, and add vertical gridlines (Chart menu > Chart Options >

Major Gridlines).

To create the chart, select the data in range F1:I11 (the blue and yellow shaded regions i

Chart Wizard, and choose a Line chart in Step 1 of the wizard.

Formulas

These are the formulas that make the chart work.

Constructing the Chart

Formulas filled

down to row 10

n y or as

formulas,

depending on

which is filled by

the user.


3/20

Select the Start series, then use Chart Type on the Chart menu to change the series to a

Select and copy range A1:D10 (shaded green above), select the chart, and use Paste Spe

add the data as New Series.

The new chart has only primary X and Y axes.

Click here to see chart with axes identified.

Double click the horizontal (time) axis; on the Scale tab, set appropriate Minimum (4/1/0

(14 days), and Minor (7 days) scale parameters. Also on the scale tab, uncheck the Value

Dates checkbox. On the Number tab, choose an appropriate date format (m/d).


4/20

Choose Chart Options on the Chart menu, click on the Axes tab, and check the Secondary

adds the list of tasks as axis labels.

The chart gains a secondary Y axis when the series is converted to a b

Select the Done series, and again use Chart Type on the Chart menu to change the series

Repeat for the Not Done series. You can use the F4 key to repeat the latest action for the

the Done series; for some reason, when you assign the Stacked Bar type to the Start seri

considers it a Clustered Bar.



5/20

Double click on the bottom time axis, and click on the Scale tab. Check the Value (Y) Axis

checkbox. This moves the numeric vertical axis from left to right (where the numbers tem

on the right axis).

The chart has primary and secondary X and Y axes.

The task labels (secondary X axis labels) are on the right, not the



6/20

Double click left (Task) axis, and click on the Scale tab. Check the Categories in Reverse

Value (Y) Axis Crosses at Maximum Category box, so the tasks are represented from top t

Number of Categories Between Tick Mark Labels box, to force Excel to display each label.

The chart has primary and secondary X and Y axes.The task labels (secondary X axis labels) are on the left, where we wa


Double click on the top time axis, and click on the Scale tab. Uncheck the Category (X) Ax

checkbox. This moves the task list to the left side of the chart.


7/20

Format the bar timeline series and milestone markers. Make the Start series invisible by c

Area on the Patterns tab. Choose appropriate colors, marker shapes, and marker sizes for

Double click the top axis. Set the scale parameters to: Minimum 4/1/01, Maximum 7/8/0

to match the bottom time axis that was formatted earlier. Even though this is a value axis

number, Excel will accept numbers in date format. On the Patterns tab, set Tick Mark Lab

top and bottom time scales must be synchronized (manually) whe

Double click on the right numerical axis. Check the Values in Reverse Order box and also

Maximum Value box. Make sure the minimum and maximum are set to 0 and 9, and unch

each, so Excel doesn't unexpectedly change the axis. If more tasks are added, b

be rescaled in tandem.On the Patterns tab, choose None for Tick Marks and Tick L


8/20

Double click on the Vert Line series, and on the Patterns tab, select None for Line and Mar

choose Plus (it goes down , but the axis is plotted in reverse order), with a Fixed Value of

bottom).

Resize the plot area, moving down the top edge to make room for a label. Double click th

point in this example), and add data labels, using the Category Name option. Double click

Alignment tab, choose Above for Label Position.

Delete the Legend, and stretch the Plot Area to fill the whole chart.


9/20

The primary Y axis and secondary Y axis are hidden.

The chart has primary and secondary X and Y axes.



10/20

I J K

Vert Line Milestone

%

Complete

#N/A 0.5 100%

#N/A 1.5 100%

ined to place the Finished

), and Percent Complete (K),

estones an prov e t e

r the formula =NA()or type

valid points on both sides,

a a. e ye ow ce s anc ors

ll of column F could be

mns G and H within the

. The value of 0 in the yellow

cribed in more detail:

sed to create the timelines in

n tasks must start andthe many tasks in a

is merely a floating bar

second series of bars are

first series is formatted to be

le approach.

, so t e oat ng ar can

d to show milestones for

Bar-Line combination chart,

hart allows us to use the


11/20

#N/A 2.5 75%

#N/A 3.5 75%

#N/A 4.5 50%

#N/A 5.5 25%

#N/A 6.5 35%

#N/A 7.5 25%

#N/A 8.5 0%0

ridlines tab > Category Axis

the table above), start the


12/20

tacked Bar type.

ial from the Edit menu to

1), Maximum (7/8/01), Major

(Y) Axis Crosses Between


13/20

Category checkbox. This

ar series.

to a Stacked Bar type.

Not Done series, but not for

s in the previous step, Excel


14/20

Crosses at Maximum Value

porarily overlap the task list

left.


15/20

rder box and uncheck the

o bottom. Enter 1 in the

nt them.

is Crosses at Maximum Value


16/20

hoosing None for Border and

the other series.

, Major Unit 14, Minor Unit 7

that is expecting a "regular"

ls to None. Note: the

the data changes.

he Category Axis Crosses at

eck the Auto box in front of

th vertical axes must

abels to hide the numbers.


17/20

ker. On the Y Error Bars tab,

9 (to stretch from top to

Vert Line series (single

the data label, and on the


18/20


19/20

Start Done Not Done Duration End Finished

Unfinishe

d Vert Line

Task 1 8/12/2008 14 0 14 8/26/2008 0.5 #N/A #N/A

Task 2 8/19/2008 21 0 21 9/9/2008 1.5 #N/A #N/A

Task 3 9/1/2008 10.5 3.5 14 9/15/2008 #N/A 2.5 #N/ATask 4 9/1/2008 21 7 28 9/29/2008 #N/A 3.5 #N/A

Task 5 9/21/2008 7 7 14 10/5/2008 #N/A 4.5 #N/A

Task 6 9/24/2008 7 21 28 10/22/2008 #N/A 5.5 #N/A

Task 7 9/24/2008 12.25 22.75 35 10/29/2008 #N/A 6.5 #N/A

Task 8 10/1/2008 8.75 26.25 35 11/5/2008 #N/A 7.5 #N/A

Task 9 10/12/2008 0 24 24 11/5/2008 #N/A 8.5 #N/A

4/14/2014 #N/A #N/A 0

8/1 8/15 8/29 9/12 9/26 10/10 10/24 11/7

Task 1

Task 2

Task 3

Task 4

Task 5

Task 6

Task 7

Task 8

Task 9


20/20

Milestone

%

Complete

0.5 100% 1

1.5 100%

2.5 75%3.5 75%

4.5 50%

5.5 25%

6.5 35%

7.5 25%

8.5 0%

11/21

Documents

My Excel Gantt Chart